####################################################################################################
##此段代码为数据整理和清洗
####################################################################################################
import pandas as pd
import os
import time
from tqdm import tqdm
import numpy as np

print("="*60)
print("【删除无效随访数据】-2025.07.29")
print("功能：根据'随访方式'和'是否失访'列筛选数据，并进行数据类型核对")
print("   - 1.筛选'电话随访'、'失访'的数据")
print("   - 2.核对指定列的数据类型和空值")
print("   - 3.收集未采用数据")
print("-" * 50)

# 记录开始时间
start_time = time.time()

# 创建临时文件夹
os.makedirs('临时_核查结果', exist_ok=True)

def validate_dtype(value, expected_type):
    """验证值是否符合预期的数据类型"""
    if pd.isna(value):
        return True  # 空值不符合要求-------改成空值不校验，F改T------------------
    
    if expected_type == 'int':
        # 检查是否为整数或可转换为整数的浮点数
        if isinstance(value, (int, np.integer)):
            return True
        if isinstance(value, float) and value.is_integer():
            return True
        return False
    
    elif expected_type == 'float':
        # 检查是否为浮点数或整数
        return isinstance(value, (float, int, np.floating, np.integer))
    
    return False

def process_data(file_name):
    
    # 步骤1: 读取文件
    print(f"步骤1: 读取文件 {file_name}...")
    try:
        df = pd.read_excel(file_name)
        total_rows = len(df)
    except Exception as e:
        print(f"  [!] 文件读取失败: {e}")
        return
    
    # 步骤2: 定义数据类型验证规则
    dtype_rules = {}
    if file_name == '高血压随访明细.xlsx':
        dtype_rules = {
            '收缩压': 'int',
            '舒张压': 'int',
            '心率': 'int',
            '每日吸烟量': 'int',
            '目标吸烟量': 'int',
            '每周运动次数': 'int',
            '每次运动时间': 'int',
            '目标运动次数': 'int',
            '目标运动时间': 'int',
            '体重': 'float',
            '目标体重': 'float',
            '身高': 'float',
            '体质指数': 'float',
            '目标体质指数': 'float'
        }
    elif file_name == '糖尿病随访明细.xlsx':
        dtype_rules = {
            '收缩压': 'int',
            '舒张压': 'int',
            '日吸烟量': 'int',
            '目标吸烟量': 'int',
            '周运动量': 'int',
            '每次运动时间': 'int',
            '目标运动量': 'int',
            '目标运动时间': 'int',
            '主食量': 'int',
            '目标主食量': 'int',
            '体重': 'float',
            '目标体重': 'float',
            '身高': 'float',
            '体质指数': 'float',
            '目标体质指数': 'float'
        }
    
    # 步骤3: 数据类型验证
    dtype_error_mask = pd.Series(False, index=df.index)
    if dtype_rules:
        print(f"步骤2: 数据类型验证...")
        for col, dtype in dtype_rules.items():
            if col in df.columns:
                # 使用apply验证每个值
                col_errors = ~df[col].apply(lambda x: validate_dtype(x, dtype))
                dtype_error_mask = dtype_error_mask | col_errors
                error_count = col_errors.sum()
                if error_count > 0:
                    print(f"  列 '{col}' 发现 {error_count} 条数据类型错误")
            else:
                print(f"  [!] 列 '{col}' 不存在，跳过验证")
        dtype_error_count = dtype_error_mask.sum()
        print(f"  错误总数: {dtype_error_count}")
    else:
        print("  未定义数据类型验证规则，跳过此步骤")
    
    # 步骤4: 筛选未采用数据
    print(f"步骤3: 筛选未采用数据 (包含'电话'或'是否失访'为是)...")
    
    # 创建进度条
    with tqdm(total=2, desc="  进度", bar_format="{l_bar}{bar}| 剩余时间: {remaining}") as pbar:
        # 筛选随访方式包含"电话"的记录
        phone_mask = df['随访方式'].str.contains('电话', case=False, na=False)
        pbar.update(1)
        
        # 筛选是否失访包含"是"的记录
        missed_mask = df['是否失访'].str.contains('是', case=False, na=False)
        pbar.update(1)
    
    # 组合筛选条件
    condition_mask = phone_mask | missed_mask
    combined_mask = condition_mask | dtype_error_mask
    
    invalid_data = df[combined_mask]
    valid_data = df[~combined_mask]
    
    invalid_count = len(invalid_data)
    valid_count = len(valid_data)
    condition_count = condition_mask.sum()
    dtype_error_count = dtype_error_mask.sum()
    
    # 步骤5: 保存未采用数据
    print(f"步骤4: 保存未采用数据到指定文件...")
    output_name = file_name.replace('.xlsx', '_已删除.xlsx')
    output_path = os.path.join('临时_核查结果', output_name)
    
    try:
        # 添加标记列
        invalid_data = invalid_data.copy()
        invalid_data['删除原因'] = ''
        invalid_data.loc[condition_mask, '删除原因'] += '电话/失访;'
        invalid_data.loc[dtype_error_mask, '删除原因'] += '数据类型错误;'
        
        invalid_data.to_excel(output_path, index=False)
    except Exception as e:
        print(f"  [!] 保存失败: {e}")
        return
    
    # 步骤6: 覆盖原始文件
    print(f"步骤5: 重新生成有效随访数据表...")
    try:
        valid_data.to_excel(file_name, index=False)
    except Exception as e:
        print(f"  [!] 覆盖失败: {e}")
        return
    
    return total_rows, invalid_count, condition_count, dtype_error_count

# 处理两个文件
files = ['高血压随访明细.xlsx', '糖尿病随访明细.xlsx']
total_stats = [0, 0, 0, 0]  # [总记录数, 总移除数, 随访条件移除数, 数据类型错误数]

for file in files:
    if os.path.exists(file):
        stats = process_data(file)
        if stats:
            total_stats[0] += stats[0]
            total_stats[1] += stats[1]
            total_stats[2] += stats[2]
            total_stats[3] += stats[3]
    else:
        print(f"\n[!] 文件不存在: {file} - 跳过处理")

# 计算总耗时
processing_time = time.time() - start_time

# 最终统计
print( "-"*50)
print("摘要")
print(f"处理文件数量        : {len(files)}")
print(f"总记录数           : {total_stats[0]:,} 条")
print(f"总移除记录数        : {total_stats[1]:,} 条 ({total_stats[1]/total_stats[0]:.1%})")
print(f"  - 因随访条件移除  : {total_stats[2]:,} 条")
print(f"  - 因数据类型错误移除: {total_stats[3]:,} 条")
print(f"总处理时间         : {processing_time:.2f} 秒")

####################################################################################################
##-还要考虑重复表格，如何提取有效体检
##-未采用的体检表保存到‘临时_核查结果’文件夹下，命名为‘体检明细表_删重删空.xlsx’
## 体检表核查程序（用于慢病规范性核查）
##-单处理‘健康体检查询统计’下载的体检表
##-如有辅助检查一并核查，无，跳过辅助检查的核查
####################################################################################################
import os
import pandas as pd
import numpy as np
import re
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Font, colors
from tqdm import tqdm
import time
import datetime
import warnings
#---------------------------------------------------------------------------------------------------
#此段代码删除无效或完整度小于35%的体检表，参与判断也是不规范-保存为‘体检明细表_删重删空.xlsx'
FILE_PATH = '体检明细表.xlsx'
OUTPUT_DIR = '临时_核查结果'
OUTPUT_FILE = os.path.join(OUTPUT_DIR, '体检明细表_删重删空.xlsx')

# 题干中所有列名
COLUMNS = [
    '选择', '个人健康档案号', '居民姓名', '性别', '身份证号', '年龄', '联系电话', 
    '体检日期', '体检医院', '体检医生', '症状', '体温', '脉率', '呼吸频率', 
    '左收缩压', '左舒张压', '右收缩压', '右舒张压', '身高', '体重', 
    '腰围', '体质指数', '老年人自我评估', '老年人生活自理评估', '老年人认知评估', 
    '老年人情感评估', '体育锻炼|频率', '体育锻炼|分钟/次', '体育锻炼|坚持年数', 
    '体育锻炼|锻炼方式', '饮食习惯', '吸烟情况|状况', '吸烟情况|日吸烟量', 
    '吸烟情况|开始吸烟年龄', '吸烟情况|戒烟年龄', '饮酒情况|频率', '饮酒情况|日饮酒量', 
    '饮酒情况|是否戒酒', '饮酒情况|戒酒年龄', '饮酒情况|开始饮酒年龄', '饮酒情况|年内醉酒', 
    '饮酒情况|饮酒种类', '职业危害|是否接触', '职业危害|工种', '职业危害|从业年数', 
    '口腔|口唇', '口腔|齿列', '口腔|咽部', '左眼视力', '右眼视力', '左眼矫正', 
    '右眼矫正', '听力', '运动功能', '眼底', '皮肤', '巩膜', '淋巴结', '肺|桶状胸', 
    '肺|呼吸音', '肺|罗音', '心脏|心率', '心脏|心律', '心脏|杂音', '腹部|压  痛', 
    '腹部|包  块', '腹部|肝  大', '腹部|脾  大', '腹部|移动性浊音', '下肢水肿', 
    '足背动脉搏动', '肛门指诊', '乳腺', '妇科|外阴', '妇科|阴道', '妇科|宫颈', 
    '妇科|宫体', '妇科|附件', '查体其他', '血常规|血红蛋白', '血常规|白细胞', 
    '血常规|血小板', '血常规|其它', '尿常规|尿蛋白', '尿常规|尿糖', '尿常规|尿酮体', 
    '尿常规|尿潜血', '尿常规|其它', '空腹血糖', '空腹血糖2', '非空腹血糖', '心电图', 
    '心电图异常', '同型半胱氨酸', '尿微量白蛋白', '大便潜血', '糖化血红蛋白', 
    '乙型肝炎表面抗原', '肝功能|血清谷丙转氨酶', '肝功能|血清谷草转氨酶', '肝功能|白蛋白', 
    '肝功能|总胆红素', '肝功能|结合胆红素', '肾功能|血清肌酐', '肾功能|血 尿 素', 
    '肾功能|血钾浓度', '肾功能|血钠浓度', '肾功能|尿酸', '血脂|总胆固醇', 
    '血脂|甘油三酯', '血脂|血清低密度脂蛋白胆固醇', '血脂|血清高密度脂蛋白胆固醇', 
    '胸部X线片', '腹部B超', '腹部B超其它', '其他B超', '其他B超其它', '宫颈涂片', 
    '辅助检查其他', '脑血管疾病', '肾脏疾病', '心脏疾病', '血管疾病', '眼部疾病', 
    '神经系统疾病', '其他系统疾病', '其他系统疾病其它', '高血压病', '高血压病其它', 
    '糖尿病', '糖尿病其它', '严重精神障碍疾病', '严重精神障碍其它', '健康评价', 
    '异常疾病|异常1', '异常疾病|异常2', '异常疾病|异常3', '异常疾病|异常4', 
    '健康指导', '健康指导其它', '危险因素控制', '目标体重(KG)', '目标腰围(CM)', 
    '建议接种疫苗', '其他危险因素', '主要用药情况'
]

os.makedirs(OUTPUT_DIR, exist_ok=True)

print('=' * 60)
print('【处理体检明细表】-2025.08.26...')
print('功能说明')
print('  -1.剔除不完整的体检表')
print('  -2.按完整度剔除重复体检表')
print('  -3.增加数据校验功能')
print('  -4.保存剔除表到临时文件夹下‘体检明细表_删重删空.xlsx’')
print('-' * 50)

df = pd.read_excel(FILE_PATH)
original_df = df.copy()
print('步骤1: 重新加载体检明细表...')
# 2. 确保列存在（添加缺失列）
for col in COLUMNS:
    if col not in df.columns:
        df[col] = np.nan

# 确保只保留需要的列
df = df[COLUMNS]

# 3. 个人健康档案号转为文本格式,日期改规定格式
df['个人健康档案号'] = df['个人健康档案号'].astype(str)
try:
    df['体检日期'] = pd.to_datetime(df['体检日期'], errors='coerce').dt.strftime('%Y-%m-%d')
except:
    df['体检日期'] = pd.to_datetime(df['体检日期'].astype(str), errors='coerce').dt.strftime('%Y-%m-%d')

# 4. 计算完整度
score_columns = [col for col in COLUMNS if col not in ['身份证号', '体检日期']]
total_columns = len(score_columns)

df['完整度'] = df[score_columns].apply(lambda row: row.notnull().sum() / total_columns, axis=1)

# 5. 记录删除原因的DataFrame
deleted_reasons = pd.Series(index=df.index, dtype=str)

# 6. 处理空值率66%（完整度<35%）
print('步骤2: 处理不完整体检表...')
single_record_ids = df['身份证号'].value_counts()
single_record_ids = single_record_ids[single_record_ids == 1].index
single_records = df[df['身份证号'].isin(single_record_ids)]

low_completeness_mask = single_records['完整度'] < 0.35
low_completeness_records = single_records[low_completeness_mask]

# 记录删除原因
deleted_reasons.loc[low_completeness_records.index] = "单条记录且空值超过65%"

# 7. 处理重复记录
# 步骤1: 处理同一天内的重复记录 
grouped_by_id_date = df.groupby(['身份证号', '体检日期'])

# 用于保存需要删除记录的索引
date_duplicate_indices = []

for (id_num, date), group in grouped_by_id_date:
    if len(group) == 1:
        continue
        
    # 找到完整度最高的记录
    max_score = group['完整度'].max()
    best_records = group[group['完整度'] == max_score]
    
    # 如果有多个同样完整的记录，取第一个（索引最小的）
    if len(best_records) > 1:
        keep_index = best_records.index[0]
        drop_indices = best_records.index[1:].tolist()
    else:
        keep_index = best_records.index[0]
        drop_indices = group.index.difference([keep_index]).tolist()
    
    # 标记这些记录需要删除
    date_duplicate_indices.extend(drop_indices)

# 记录删除原因
deleted_reasons.loc[date_duplicate_indices] = "同一天内重复且数据不全"

# 步骤2: 处理不同日期的记录 
print('步骤3: 处理重复体检表...')
grouped_by_id = df.groupby('身份证号')

id_duplicate_indices = []

for id_num, group in grouped_by_id:
    if len(group) == 1:
        continue
        
    # 首先移除已经标记为删除的记录
    group = group[~group.index.isin(low_completeness_records.index) &  ~group.index.isin(date_duplicate_indices)]
    
    if len(group) <= 1:
        continue
        
    # 按完整度（降序）和体检日期（降序）排序
    group_sorted = group.sort_values( by=['完整度', '体检日期'], ascending=[False, False])
    
    # 保留第一条记录（完整度最高且日期最近）
    keep_index = group_sorted.index[0]
    drop_indices = group_sorted.index[1:].tolist()
    
    # 标记这些记录需要删除
    id_duplicate_indices.extend(drop_indices)

# 记录删除原因
deleted_reasons.loc[id_duplicate_indices] = "重复记录，保留最全最新记录"

# 8. 整合所有删除记录
all_delete_indices = list(set(
    low_completeness_records.index.tolist() + 
    date_duplicate_indices + 
    id_duplicate_indices
))

# 创建最终结果
result = df[~df.index.isin(all_delete_indices)].copy()
deleted_records = df[df.index.isin(all_delete_indices)].copy()

# 9. 为删除记录添加删除原因
deleted_records['删除原因'] = deleted_reasons.loc[deleted_records.index]

# 10. 整理结果
result = result.drop(columns=['完整度'])
result = result.reset_index(drop=True)

# 11. 确保所有格式符合要求
for df_to_process in [result, deleted_records]:
    # 确保个人健康档案号为文本格式
    df_to_process['个人健康档案号'] = df_to_process['个人健康档案号'].astype(str)
    try:
        df_to_process['体检日期'] = pd.to_datetime( df_to_process['体检日期'], errors='coerce').dt.strftime('%Y-%m-%d')
    except:
        # 如果转换失败，尝试处理异常日期
        df_to_process['体检日期'] = ( pd.to_datetime(df_to_process['体检日期'].astype(str), errors='coerce').dt.strftime('%Y-%m-%d') )

# 12. 数据校验（根据最新要求修改）-------------------08.26
print('步骤4: 执行数据校验...')
validation_rules = {
    # 所有字段都允许为空，不为空时才检查数据类型
    '体温': (float),
    '脉率': (int),
    '呼吸频率': (int),
    '左收缩压': (int),
    '左舒张压': (int),
    '右收缩压': (int),
    '右舒张压': (int),
    '身高': (float),
    '体重': (float),
    '腰围': (float),
    '体育锻炼|分钟/次': (int),
    '体育锻炼|坚持年数': (float),
    '吸烟情况|日吸烟量': (int),
    '吸烟情况|开始吸烟年龄': (int),
    '吸烟情况|戒烟年龄': (int),
    '饮酒情况|日饮酒量': (float),
    '饮酒情况|戒酒年龄': (int),
    '饮酒情况|开始饮酒年龄': (int),
    '左眼视力': (float),
    '右眼视力': (float),
    '左眼矫正': (float),
    '右眼矫正': (float),
    '心脏|心率': (int)
}

invalid_records = []

# 校验每条记录
for idx, row in result.iterrows():
    invalid_columns = []
    
    for col, dtype in validation_rules.items():
        # 跳过不存在的列
        if col not in row:
            continue
            
        value = row[col]
        is_empty = pd.isna(value) or str(value).strip() in ['', 'nan', 'None', 'NaT']
        
        # 如果为空，跳过检查（所有字段都允许为空）
        if is_empty:
            continue
            
        # 检查数据类型
        try:
            if dtype == int:
                # 尝试转换为整数
                int_value = int(float(value))  # 先转为float再转int，处理小数形式的整数
                # 验证是否为整数（防止小数通过）
                if float(value) != int_value:
                    invalid_columns.append(f"{col}应为整数")
            elif dtype == float:
                # 尝试转换为浮点数
                float_value = float(value)
                # 特殊处理：视力值应在合理范围内
                if col in ['左眼视力', '右眼视力', '左眼矫正', '右眼矫正']:
                    if float_value < 0 or float_value > 10:
                        invalid_columns.append(f"{col}值超出合理范围(0-10)")
        except (ValueError, TypeError):
            invalid_columns.append(f"{col}应为{dtype.__name__}")
    
    # 如果发现无效列，添加到无效记录列表
    if invalid_columns:
        new_record = row.copy()
        new_record['删除原因'] = "数据类型有误：" + "，".join(invalid_columns)
        invalid_records.append(new_record)

# 处理无效记录
if invalid_records:
    invalid_df = pd.DataFrame(invalid_records)
    # 从结果中移除无效记录
    result = result[~result.index.isin(invalid_df.index)]
    # 添加到删除记录
    deleted_records = pd.concat([deleted_records, invalid_df], ignore_index=True)
    print(f"发现{len(invalid_df)}条数据类型无效的记录")
else:
    print("未发现数据类型无效的记录")

# 13. 保存结果-覆盖原始文件
print('步骤5: 重新保存体检明细表...')
result.to_excel(FILE_PATH, index=False)

# 保存被删除的数据
deleted_records.to_excel(OUTPUT_FILE, index=False)

# 14. 打印处理结果
print('-' * 50)
print(f"原始体检表数: {len(original_df)}...")
print(f"删除体检表数: {len(deleted_records)}...")
#---------------------------------------------------------------------------------------------------
#---------------------------------------------------------------------------------------------------
# 此段用于核查体检表
# 忽略FutureWarning
warnings.simplefilter(action='ignore', category=FutureWarning)

# 简化美化输出函数
def print_header(title): print("="*60 + f"\n{title.upper()}...")
def print_step(step_num, step_desc): print(f"步骤{step_num}: {step_desc}...")
def print_success(message): print(f"  ✅ {message}")
def print_warning(message): print(f"  ⚠️ {message}")
def print_error(message): print(f"  ❌ {message}")
def print_info(message): print(f"  ➤ {message}")

def calculate_age(id_card, exam_date):
    """根据身份证号和体检日期计算年龄（逻辑不变）"""
    if pd.isna(id_card) or pd.isna(exam_date) or not isinstance(id_card, str) or len(id_card) < 14:
        return None

    try:
        # 解析体检日期
        exam_date = pd.to_datetime(exam_date)

        # 从身份证号提取出生日期
        if len(id_card) == 18:
            birth_date = datetime.datetime.strptime(id_card[6:14], '%Y%m%d')
        elif len(id_card) == 15:
            birth_date = datetime.datetime.strptime('19' + id_card[6:12], '%Y%m%d')
        else:
            return None

        # 计算年龄
        age = exam_date.year - birth_date.year - ((exam_date.month, exam_date.day) < (birth_date.month, birth_date.day))
        return age
    except:
        return None

def check_physical_exam_data(df):
    """核查体检数据并标记问题（仅显示修改规则）"""
    print_step(1, "核查体检数据")

    # ...（初始化列和变量保持不变）...
    df['来源'] = '其他'
    df['积分分值'] = 0
    df['体检表是否规范'] = ''
    df['核查结果汇总'] = ''
    df['核查现存健康问题'] = ''
    df['核查健康评价'] = ''
    df['核查健康指导'] = ''
    df['核查其他空错项'] = ''

    # 创建存储黄色单元格的集合 (行索引, 列名)
    yellow_cells = set()

    # 定义疾病列
    disease_cols = ['异常疾病|异常1', '异常疾病|异常2', '异常疾病|异常3', '异常疾病|异常4']

    # 遍历每一行进行核查
    for idx, row in tqdm(df.iterrows(), total=len(df), desc="  进度"):
        results = []
        health_problem_issues = []  # 核查现存健康问题
        health_eval_issues = []     # 核查健康评价
        health_guide_issues = []    # 核查健康指导
        other_issues = []           # 核查其他空错项
        score_deduction = 0         # 积分扣除值

        # 检查是否跳过某些核查项（当血常规|血红蛋白为空时）
        skip_checks = pd.isna(row.get('血常规|血红蛋白'))

        # 计算年龄
        id_card = row.get('身份证号')
        exam_date = row.get('体检日期')
        age = calculate_age(id_card, exam_date) if id_card and exam_date else None

        # 计算年龄（按年份计算）
        age_2 = None  
        if id_card and exam_date:
            # 从身份证号提取出生年份
            if isinstance(id_card, str) and len(id_card) in (15, 18):
                try:
                    if len(id_card) == 18:
                        birth_year = int(id_card[6:10])
                    else:  # 15位身份证
                        birth_year = int('19' + id_card[6:8])
                    
                    # 从体检日期提取年份
                    if isinstance(exam_date, str):
                        exam_year = int(exam_date[:4])
                    elif isinstance(exam_date, datetime.datetime):
                        exam_year = exam_date.year
                    else:
                        exam_year = None
                    
                    if exam_year:
                        age_2 = exam_year - birth_year
                except (ValueError, TypeError):
                    age_2 = None
        
        # 获取常用字段
        other_risk = str(row.get('其他危险因素', ''))
        risk_factors = str(row.get('危险因素控制', ''))
        drinking_freq = str(row.get('饮酒情况|频率', ''))
        quit_drinking = str(row.get('饮酒情况|是否戒酒', ''))
        bmi = row.get('体质指数')
        gender = row.get('性别')
        waist_val = row.get('腰围', 0)

        # 1. ============================================================================体温检查(35-38,计1分)
        temp = row.get('体温')
        if not pd.isna(temp):
            try:
                temp_val = float(temp)
                if temp_val < 35 or temp_val > 38:
                    results.append("体温")
                    other_issues.append("体温异常")
                    yellow_cells.add((idx, '体温'))
                    score_deduction += 1
            except:
                results.append("体温")
                other_issues.append("体温异常")
                yellow_cells.add((idx, '体温'))
                score_deduction += 1

        # 2. ============================================================================脉率检查（35-170，计1分）
        pulse = row.get('脉率')
        if not pd.isna(pulse):
            try:
                pulse_val = float(pulse)
                if pulse_val < 35 or pulse_val > 170:
                    results.append("脉率")
                    other_issues.append("脉率异常")
                    yellow_cells.add((idx, '脉率'))
                    score_deduction += 1
            except:
                results.append("脉率")
                other_issues.append("脉率异常")
                yellow_cells.add((idx, '脉率'))
                score_deduction += 1

        # 3. ============================================================================呼吸频率检查（12-30，计1分）
        resp = row.get('呼吸频率')
        if not pd.isna(resp):
            try:
                resp_val = float(resp)
                if resp_val < 12 or resp_val > 30:
                    results.append("呼吸")
                    other_issues.append("呼吸频率异常")
                    yellow_cells.add((idx, '呼吸频率'))
                    score_deduction += 1
            except:
                results.append("呼吸")
                other_issues.append("呼吸频率异常")
                yellow_cells.add((idx, '呼吸频率'))
                score_deduction += 1

        # 4-7. ============================================================================血压检查（70-240/35-135，计1分）
        bp_cols = ['左收缩压', '左舒张压', '右收缩压', '右舒张压']
        bp_issues = False

        # 检查是否同时为空
        for col in bp_cols:
            if col in df.columns:
                val = row.get(col)
                if pd.isna(val) or not isinstance(val, (int, float)) or val != int(val):
                    bp_issues = True
                    yellow_cells.add((idx, col))

        if not bp_issues:
            ls = row.get('左收缩压')
            ld = row.get('左舒张压')
            rs = row.get('右收缩压')
            rd = row.get('右舒张压')

            if ls <= ld or rs <= rd:
                bp_issues = True
            if ls < 70 or ls > 240 or rs < 70 or rs > 240:
                bp_issues = True
            if ld < 35 or ld > 135 or rd < 35 or rd > 135:
                bp_issues = True

        if bp_issues:
            results.append("血压")
            other_issues.append("血压值异常")
            for col in bp_cols:
                if col in df.columns:
                    yellow_cells.add((idx, col))
            score_deduction += 1

        # 8. ============================================================================身高检查（90-200，计1分）
        height = row.get('身高')
        if not pd.isna(height):
            try:
                height_val = float(height)
                if height_val < 90 or height_val > 200:
                    results.append("身高")
                    other_issues.append("身高异常")
                    yellow_cells.add((idx, '身高'))
                    score_deduction += 1
            except:
                results.append("身高")
                other_issues.append("身高异常")
                yellow_cells.add((idx, '身高'))
                score_deduction += 1

        # 9. ============================================================================体重检查（30-150，计1分）
        weight = row.get('体重')
        if not pd.isna(weight):
            try:
                weight_val = float(weight)
                if weight_val < 30 or weight_val > 150:
                    results.append("体重")
                    other_issues.append("体重异常")
                    yellow_cells.add((idx, '体重'))
                    score_deduction += 1
            except:
                results.append("体重")
                other_issues.append("体重异常")
                yellow_cells.add((idx, '体重'))
                score_deduction += 1

        # 10. ============================================================================腰围检查（45-150，计1分）
        waist = row.get('腰围')
        if not pd.isna(waist):
            try:
                waist_val = float(waist)
                if waist_val < 45 or waist_val > 150:
                    results.append("腰围")
                    other_issues.append("腰围异常")
                    yellow_cells.add((idx, '腰围'))
                    score_deduction += 1
            except:
                results.append("腰围")
                other_issues.append("腰围异常")
                yellow_cells.add((idx, '腰围'))
                score_deduction += 1

        # 11-13. ============================================================================体质指数相关检查（漏错指，计1分）
        risk_factors = str(row.get('危险因素控制', ''))
        other_risk = str(row.get('其他危险因素', ''))

        if not pd.isna(bmi):
            try:
                bmi_val = float(bmi)
                if bmi_val < 18.5:
                    if "增" not in other_risk:
                        results.append("少增重")
                        health_guide_issues.append("少增重建议")
                        yellow_cells.add((idx, '其他危险因素'))
                        score_deduction += 1
                elif bmi_val >= 24:
                    if ("减体重" not in risk_factors) and ("减体重" not in other_risk):
                        results.append("少减重")
                        health_guide_issues.append("少减重建议")
                        yellow_cells.add((idx, '危险因素控制'))
                        yellow_cells.add((idx, '其他危险因素'))
                        score_deduction += 1
                else:  # 18.5 <= BMI < 24
                    if ("减体重" in risk_factors) or ("减体重" in other_risk):
                        results.append("多减重")
                        health_guide_issues.append("多减重建议")
                        yellow_cells.add((idx, '危险因素控制'))
                        yellow_cells.add((idx, '其他危险因素'))
                        score_deduction += 1
            except:
                pass

        # 14-15. ============================================================================体育锻炼相关（错漏指，计1分）
        exercise_freq = str(row.get('体育锻炼|频率', ''))
        if "每天" not in exercise_freq:
            if "锻炼" not in risk_factors:
                results.append("少锻炼")
                health_guide_issues.append("少锻炼建议")
                yellow_cells.add((idx, '危险因素控制'))
                score_deduction += 1
        else:
            if "锻炼" in risk_factors:
                results.append("多锻炼")
                health_guide_issues.append("多锻炼建议")
                yellow_cells.add((idx, '危险因素控制'))
                score_deduction += 1

        # 16-17. ============================================================================吸烟情况（错漏指，计1分）
        smoking = str(row.get('吸烟情况|状况', ''))
        if smoking == "吸烟":
            if "戒烟" not in risk_factors:
                results.append("少戒烟")
                health_guide_issues.append("少戒烟建议")
                yellow_cells.add((idx, '危险因素控制'))
                score_deduction += 1
        else:
            if "戒烟" in risk_factors:
                results.append("多戒烟")
                health_guide_issues.append("多戒烟建议")
                yellow_cells.add((idx, '危险因素控制'))
                score_deduction += 1

        # 18-20. ============================================================================饮酒情况（错漏指，计1分）
        drinking_freq = str(row.get('饮酒情况|频率', ''))
        quit_drinking = str(row.get('饮酒情况|是否戒酒', ''))
        start_age = row.get('饮酒情况|开始饮酒年龄')

        if drinking_freq != "从不":
            if quit_drinking == "未戒酒":  # 2025.06.18
                if "健康饮酒" not in risk_factors:
                    results.append("少健康饮酒")
                    health_guide_issues.append("少健康饮酒建议")
                    yellow_cells.add((idx, '危险因素控制'))
                    score_deduction += 1
                if not pd.isna(start_age):
                    try:
                        if float(start_age) <= 10:
                            results.append("饮酒年龄")
                            other_issues.append("饮酒年龄异常")
                            yellow_cells.add((idx, '饮酒情况|频率'))
                            score_deduction += 0.5
                    except:
                        results.append("饮酒年龄")
                        other_issues.append("饮酒年龄异常")
                        yellow_cells.add((idx, '饮酒情况|频率'))
                        score_deduction += 0.5
            elif quit_drinking == "已戒酒":
                if "健康饮酒" in risk_factors:
                    results.append("多健康饮酒")
                    health_guide_issues.append("多健康饮酒建议")
                    yellow_cells.add((idx, '危险因素控制'))
                    score_deduction += 1
        else:
            if "健康饮酒" in risk_factors:
                results.append("多健康饮酒")
                health_guide_issues.append("多健康饮酒建议")
                yellow_cells.add((idx, '危险因素控制'))
                score_deduction += 1

        # 21. ============================================================================饮食习惯（错漏指，计1分）
        diet = str(row.get('饮食习惯', ''))
        diet_options = ["荤素均衡", "素食为主", "荤食为主"]
        count = sum(1 for option in diet_options if option in diet)
        if count != 1:
            results.append("饮食")
            other_issues.append("饮食习惯选择错误")
            yellow_cells.add((idx, '饮食习惯'))
            score_deduction += 1

        # 22. ============================================================================饮食危险因素（错项，计1分）
        if any(keyword in diet for keyword in ["素食", "荤食", "嗜"]):
            if "饮食" not in risk_factors:
                results.append("饮食")
                health_guide_issues.append("饮食危险因素未评价")
                yellow_cells.add((idx, '危险因素控制'))
                score_deduction += 1

        # 23. ============================================================================荤素均衡但危险因素控制有饮食（错漏指，计1分）
        if diet == '荤素均衡,' and "饮食" in risk_factors:
            results.append("饮食多评价")
            health_guide_issues.append("多饮食指导")
            yellow_cells.add((idx, '危险因素控制'))
            score_deduction += 1

        # 24-25. =========================================================================视力检查（空错项，计1分）
        eye_cols = ['左眼视力', '左眼矫正', '右眼视力', '右眼矫正']
        eye_issue = False

        # 检查是否同时为空
        if (pd.isna(row.get('左眼视力')) and pd.isna(row.get('左眼矫正'))) or \
                (pd.isna(row.get('右眼视力')) and pd.isna(row.get('右眼矫正'))):
            eye_issue = True

        # 检查数值范围
        for col in eye_cols:
            if col in df.columns:
                val = row.get(col)
                if not pd.isna(val):
                    try:
                        val_num = float(val)
                        if val_num != 0 and (val_num < 4.0 or val_num > 5.3):
                            eye_issue = True
                    except:
                        eye_issue = True

        if eye_issue:
            results.append("视力")
            other_issues.append("视力检查异常")
            for col in eye_cols:
                if col in df.columns:
                    yellow_cells.add((idx, col))
            score_deduction += 1

        # 26-28. ==========================================================================心率脉率检查，同时考虑房颤与房扑（错漏评，计0.5分）
        heart_rate = row.get('心脏|心率')
        pulse_rate = row.get('脉率')

        if not pd.isna(heart_rate):
            try:
                hr_val = float(heart_rate)

                # ===== 优化后的条件：检查异常疾病列是否包含"颤"或"扑" =====
                has_fibrillation = False
                for col in disease_cols:
                    if col in df.columns:
                        disease_text = str(row.get(col, ''))
                        if '颤' in disease_text or '扑' in disease_text:
                            has_fibrillation = True
                            break

                if has_fibrillation:
                    # 如果发现"颤"或"扑"，跳过其他所有心率检查
                    pass
                else:
                    # 原有心率范围检查(仅在无"颤"或"扑"时执行)
                    if hr_val < 35 or hr_val > 170 or hr_val != int(hr_val):
                        results.append("心率")
                        health_eval_issues.append("心率异常")
                        yellow_cells.add((idx, '心脏|心率'))
                        score_deduction += 1

                    # 心率与脉率差异(仅在无"颤"或"扑"时执行)
                    if not pd.isna(pulse_rate):
                        try:
                            pr_val = float(pulse_rate)
                            if abs(hr_val - pr_val) > 20:
                                results.append("脉心率差")
                                health_eval_issues.append("心率脉率差异过大")
                                yellow_cells.add((idx, '心脏|心率'))
                                yellow_cells.add((idx, '脉率'))
                                score_deduction += 0.5
                        except:
                            pass

                    # 过缓疾病检查(仅在无"颤"或"扑"时执行)
                    if hr_val < 60 and pulse_rate < 60:
                        has_bradycardia = any(
                            "过缓" in str(row.get(col, '')) for col in disease_cols if col in df.columns)
                        if not has_bradycardia:
                            results.append("无过缓")
                            health_eval_issues.append("心率过缓未评价")
                            for col in disease_cols:
                                if col in df.columns:
                                    yellow_cells.add((idx, col))
                            score_deduction += 0.5
            except:
                results.append("心率")
                health_eval_issues.append("心率异常")
                yellow_cells.add((idx, '心脏|心率'))
                score_deduction += 1

        # 29-30. ============================================================================健康指导检查（7.30增肺）
        other_disease = str(row.get('其他系统疾病其它', ''))
        health_guide = str(row.get('健康指导', ''))

        if "血压" in other_disease or "糖" in other_disease or "阻肺" in other_disease or \
            "慢支" in other_disease or "慢性支" in other_disease or "肺气肿" in other_disease:
            if "纳入" not in health_guide:
                results.append("健康指导")
                health_guide_issues.append("少纳入管理")
                yellow_cells.add((idx, '健康指导'))
                score_deduction += 1
        else:
            if "纳入" in health_guide:
                results.append("健康指导")
                health_guide_issues.append("多纳入管理")
                yellow_cells.add((idx, '健康指导'))
                score_deduction += 1

        # 31-33. ============================================================================目标体重检查（错漏指，计0.5分）
        bmi = row.get('体质指数')
        target_weight = row.get('目标体重(KG)')
        weight_val = row.get('体重')

        if not pd.isna(bmi) and bmi >= 24:
            if not pd.isna(target_weight) and not pd.isna(weight_val):
                try:
                    # 新增功能：体重≤50时，减重<1kg标记黄色
                    if float(weight_val) <= 50:
                        if (float(weight_val) - float(target_weight)) < 1:
                            results.append("目标体重")
                            health_guide_issues.append("目标体重不合理")
                            yellow_cells.add((idx, '目标体重(KG)'))
                            score_deduction += 0.5
                    elif float(weight_val) - float(target_weight) < 2:
                        results.append("目标体重")
                        health_guide_issues.append("目标体重不合理")
                        yellow_cells.add((idx, '目标体重(KG)'))
                        score_deduction += 0.5
                except:
                    # 异常处理保持不变
                    results.append("目标体重")
                    health_guide_issues.append("目标体重不合理")
                    yellow_cells.add((idx, '目标体重(KG)'))
                    score_deduction += 0.5

        # 34. ============================================================================防跌倒检查（错漏指，计0.3分）
        if age is not None and age_2 >= 70:
            if "防跌倒" not in other_risk:
                results.append("少防跌倒")
                health_guide_issues.append("防跌倒建议缺失")
                yellow_cells.add((idx, '其他危险因素'))
                score_deduction += 0.3
        elif "防跌倒" in other_risk:
            results.append("多防跌倒")
            health_guide_issues.append("多防跌倒建议")
            yellow_cells.add((idx, '其他危险因素'))
            score_deduction += 0.3

        # 35. ============================================================================血压核查规则（错漏评，计1分）
        other_diseases = str(row.get('其他系统疾病其它', '')).strip()
        left_systolic = row.get('左收缩压')
        left_diastolic = row.get('左舒张压')
        right_systolic = row.get('右收缩压')
        right_diastolic = row.get('右舒张压')

        # 检查左右臂血压值是否存在
        bp_values_valid = all(
            pd.notna(bp) and isinstance(bp, (int, float)) for bp in [left_systolic, left_diastolic, right_systolic, right_diastolic]
        )

        if bp_values_valid:
            # 规则1：非血压人群，血压偏高（任一臂超标）
            if '血压' not in other_diseases:
                # 判断是否有任一臂符合血压偏高条件
                # 规则2：非血压人群，血压危急值（任一臂超标）
                bp_critical = (left_systolic >= 180 or left_diastolic >= 110 or
                               right_systolic >= 180 or right_diastolic >= 110)

                if bp_critical:
                    contains_critical = any('危急值' in re.sub(r'\s+', ' ', str(row.get(col, ''))).strip()
                                            for col in disease_cols if col in df.columns
                                            )
                    if not contains_critical:
                        results.append("血压危急漏评价")
                        health_eval_issues.append("血压危急漏评价")
                        yellow_cells.add((idx, '左收缩压'))
                        yellow_cells.add((idx, '左舒张压'))
                        yellow_cells.add((idx, '右收缩压'))
                        yellow_cells.add((idx, '右舒张压'))
                        score_deduction += 1
                elif (left_systolic >= 140 or  left_diastolic >= 90 or  right_systolic >= 140 or right_diastolic >= 90):
                    contains_high = any('血压偏高' in re.sub(r'\s+', ' ', str(row.get(col, ''))).strip()
                                        for col in disease_cols if col in df.columns
                                        )
                    if not contains_high:
                        results.append("血压少评价")
                        health_eval_issues.append("血压少评价")
                        yellow_cells.add((idx, '左收缩压'))
                        yellow_cells.add((idx, '左舒张压'))
                        yellow_cells.add((idx, '右收缩压'))
                        yellow_cells.add((idx, '右舒张压'))
                        score_deduction += 1
            # 血压人群
            else:
                # 规则3：血压人群控制不满意或控制不达标（任一臂超标）
                bp_unsatisfactory = (left_systolic >= 150 or left_diastolic >= 90 or
                                     right_systolic >= 150 or right_diastolic >= 90)

                if bp_unsatisfactory:
                    contains_unsatisfactory = any(
                        '血压控制不满意' in re.sub(r'\s+', ' ', str(row.get(col, ''))).strip() or
                        '血压控制不达标' in re.sub(r'\s+', ' ', str(row.get(col, ''))).strip()
                        for col in disease_cols if col in df.columns)
                    if not contains_unsatisfactory:
                        results.append("血压控制不满意/不达标漏评价")
                        health_eval_issues.append("血压控制不满意/不达标漏评价")
                        yellow_cells.add((idx, '左收缩压'))
                        yellow_cells.add((idx, '左舒张压'))
                        yellow_cells.add((idx, '右收缩压'))
                        yellow_cells.add((idx, '右舒张压'))
                        score_deduction += 1

                # 规则4：血压人群控制满意（双臂均达标）
                bp_satisfactory = (left_systolic < 150 and left_diastolic < 90 and
                                   right_systolic < 150 and right_diastolic < 90)

                if bp_satisfactory:
                    contains_satisfactory = any('血压控制满意' in re.sub(r'\s+', ' ', str(row.get(col, ''))).strip()
                                                for col in disease_cols if col in df.columns)
                    if contains_satisfactory:
                        results.append("血压控制满意多评价")
                        health_eval_issues.append("血压控制满意多评价")
                        yellow_cells.add((idx, '左收缩压'))
                        yellow_cells.add((idx, '左舒张压'))
                        yellow_cells.add((idx, '右收缩压'))
                        yellow_cells.add((idx, '右舒张压'))
                        score_deduction += 1

                    contains_satisfactory = any('血压偏高' in re.sub(r'\s+', ' ', str(row.get(col, ''))).strip()
                                                for col in disease_cols if col in df.columns)
                    if contains_satisfactory:
                        results.append("血压多评价")
                        health_eval_issues.append("范围内血压多评价")
                        yellow_cells.add((idx, '左收缩压'))
                        yellow_cells.add((idx, '左舒张压'))
                        yellow_cells.add((idx, '右收缩压'))
                        yellow_cells.add((idx, '右舒张压'))
                        score_deduction += 1    

        # 36.============================================================================心电图异常与健康评价核查规则（错漏指，计0.3分）
        # 只有当血常规|血红蛋白不为空时才执行此核查
        if not skip_checks:
            ecg_value = row.get('心电图异常')
            # 处理各种可能的空值情况
            if pd.isna(ecg_value) or ecg_value is None or str(ecg_value).strip() == '':
                ecg_abnormal = ''
            else:
                ecg_abnormal = re.sub(r'\s+', ' ', str(ecg_value)).strip()      # 清理字符串：去除不可见字符和多余空格

            # 仅当'心电图异常'列有有效文字且不是"窦性心律"时才进行核对
            if ecg_abnormal and ecg_abnormal != '窦性心律':
                # 定义需要检查的关键字列表
                ecg_keywords = [
                    '不齐', '过缓', '过速', '阻滞', '颤', '早',
                    '高电压', '低电压', '电轴',
                    '肥', 'T', '梗', '起'
                ]

                # 检查异常疾病列是否包含任何心电图关键字
                keyword_found = False
                keyword_found_two = False

                # 遍历所有心电图关键字
                for keyword in ecg_keywords:
                    if keyword in ecg_abnormal:
                        keyword_found_two = True
                        # 检查四个异常疾病列是否包含此关键字
                        for col in disease_cols:
                            if col in df.columns:
                                disease_value = str(row.get(col, ''))
                                cleaned_disease = re.sub(r'\s+', ' ', disease_value).strip()
                                if keyword in cleaned_disease:
                                    keyword_found = True
                                    break
                        if keyword_found:
                            break

                # 如果心电图有异常但疾病列未标注
                if not keyword_found and keyword_found_two:
                    if "心电图异常评价" not in results:
                        results.append("心电图漏评价")
                        health_eval_issues.append("心电图漏评价")
                        yellow_cells.add((idx, '心电图异常'))
                        score_deduction += 0.3

        # 37.============================================================================腹部B超其它异常与健康评价核查规则（错漏指，计0.3分）
        # 只有当血常规|血红蛋白不为空时才执行此核查
        if not skip_checks:
            ultrasound_value = row.get('腹部B超其它')
            if pd.isna(ultrasound_value) or ultrasound_value is None or str(ultrasound_value).strip() == '':
                ultrasound_other = ''
            else:
                ultrasound_other = re.sub(r'\s+', ' ', str(ultrasound_value)).strip()

            if ultrasound_other:
                # 规则37修改：根据是否包含"未见占位"调整关键字列表
                if "未见占位" in ultrasound_other:
                    ultrasound_keywords = ['结石', '切除', '肿大', '水', '囊肿', '脂肪肝', '息肉', '血管瘤', '回声', '钙', '胆囊炎', '硬化']
                else:
                    ultrasound_keywords = ['结石', '切除', '占位', '肿大', '水', '囊肿', '显示', 
                                          '脂肪肝', '息肉', '血管瘤', '回声', '钙', '胆囊炎', '硬化']

                keyword_found = False
                keyword_found_two = False

                for keyword in ultrasound_keywords:
                    if keyword in ultrasound_other:
                        keyword_found_two = True
                        # 检查四个异常疾病列是否包含此关键字
                        for col in disease_cols:
                            if col in df.columns:
                                disease_value = str(row.get(col, ''))
                                cleaned_disease = re.sub(r'\s+', ' ', disease_value).strip()
                                if keyword in cleaned_disease:
                                    keyword_found = True
                                    break
                        if keyword_found:
                            break

                # 如果腹部B超有异常但疾病列未标注
                if not keyword_found and keyword_found_two:
                    if "腹部B超评价" not in results:
                        results.append("B超漏评价")
                        health_eval_issues.append("B超漏评价")
                        yellow_cells.add((idx, '腹部B超其它'))
                        score_deduction += 0.3

        # 38.============================================================================空腹血糖核查规则（错漏评，计1分）
        crowd_label = str(row.get('其他系统疾病其它', '')).strip()
        fasting_bs = row.get('空腹血糖')
        if pd.notna(fasting_bs) and isinstance(fasting_bs, (int, float)):
            # 规则1：非糖尿病人群，空腹血糖在（6.1, 7)区间
            if '糖' not in crowd_label:
                # 规则1：空腹血糖≥6.1且<7且疾病列不含"受损"
                if 6.1 <= fasting_bs < 7:
                    contains_damaged = any('受损' in re.sub(r'\s+', ' ', str(row.get(col, ''))).strip()
                                           for col in disease_cols if col in df.columns
                                           )
                    if not contains_damaged:
                        results.append("空腹血糖受损漏评价")
                        health_eval_issues.append("空腹血糖受损漏评价")
                        yellow_cells.add((idx, '空腹血糖'))
                        score_deduction += 1

                # 规则2：空腹血糖≥7且疾病列不含"糖偏高"
                if fasting_bs >= 7:
                    contains_high = any('糖偏高' in re.sub(r'\s+', ' ', str(row.get(col, ''))).strip()
                                        for col in disease_cols if col in df.columns
                                        )
                    if not contains_high:
                        results.append("血糖偏高漏评价")
                        health_eval_issues.append("血糖偏高漏评价")
                        yellow_cells.add((idx, '空腹血糖'))
                        score_deduction += 1

                # 规则3：空腹血糖危急值(≤3.9或≥16.7)且疾病列不含"糖危急值"
                if fasting_bs <= 3.9 or fasting_bs >= 16.7:
                    contains_critical = any('糖危急值' in re.sub(r'\s+', ' ', str(row.get(col, ''))).strip()
                                            for col in disease_cols if col in df.columns
                                            )
                    if not contains_critical:
                        results.append("血糖危急值漏评价")
                        health_eval_issues.append("血糖危急值漏评价")
                        yellow_cells.add((idx, '空腹血糖'))
                        score_deduction += 1

            # 糖尿病人群
            else:
                # 规则4：空腹血糖异常(≥7或≤3.9)且疾病列不含"血糖控制不满意"或"血糖控制不达标"
                if fasting_bs >= 7 or fasting_bs <= 3.9:
                    contains_unsatisfactory = any(
                        '血糖控制不满意' in re.sub(r'\s+', ' ', str(row.get(col, ''))).strip() or
                        '血糖控制不达标' in re.sub(r'\s+', ' ', str(row.get(col, ''))).strip()
                        for col in disease_cols if col in df.columns)
                    if not contains_unsatisfactory:
                        results.append("血糖控制不满意/不达标漏评价")
                        health_eval_issues.append("血糖控制不满意/不达标漏评价")
                        yellow_cells.add((idx, '空腹血糖'))
                        score_deduction += 1

                # 规则5：空腹血糖在正常范围(3.9-7)且疾病列含"血糖控制满意"
                if 3.9 < fasting_bs < 7:
                    contains_satisfactory = any('血糖控制满意' in re.sub(r'\s+', ' ', str(row.get(col, ''))).strip()
                                                for col in disease_cols if col in df.columns)
                    if contains_satisfactory:
                        results.append("血糖控制满意多评价")
                        health_eval_issues.append("血糖控制满意多评价")
                        yellow_cells.add((idx, '空腹血糖'))
                        score_deduction += 1

        # 39. ============================================================================症状列核查（错空项，计0.5分）
        symptoms = str(row.get('症状', ''))
        if '无症状' in symptoms and symptoms != '无症状,':
            results.append("症状")
            other_issues.append("症状填写错误")
            yellow_cells.add((idx, '症状'))
            score_deduction += 0.5

        # 40. ============================================================================有症状未摄片核查（错漏项，计0.1分）
        # 只有当血常规|血红蛋白不为空时才执行此核查
        if not skip_checks:
            if any(symptom in symptoms for symptom in ['胸痛', '咳嗽', '咳痰', '呼吸困难']):
                chest_xray = row.get('胸部X线片')
                if pd.isna(chest_xray):
                    results.append("有症状未摄片")
                    other_issues.append("有症状未摄片")
                    yellow_cells.add((idx, '症状'))
                    yellow_cells.add((idx, '胸部X线片'))
                    score_deduction += 0.1

        # 41. ============================================================================老年评估核查（错漏项，计1分）
        if age is not None and age > 65:
            elder_assess = row.get('老年人自我评估')
            cognitive_assess = row.get('老年人生活自理评估')
            if pd.isna(elder_assess) or pd.isna(cognitive_assess):
                results.append("老年评估")
                other_issues.append("老年评估缺失")
                if pd.isna(elder_assess):
                    yellow_cells.add((idx, '老年人自我评估'))
                if pd.isna(cognitive_assess):
                    yellow_cells.add((idx, '老年人生活自理评估'))
                score_deduction += 1

        # 42. ============================================================================已戒烟者年龄核查（错漏，计0.3分）
        smoking_status = str(row.get('吸烟情况|状况', ''))
        if smoking_status == '已戒烟':
            start_smoke_age = row.get('吸烟情况|开始吸烟年龄')
            quit_smoke_age = row.get('吸烟情况|戒烟年龄')

            if not pd.isna(start_smoke_age) and float(start_smoke_age) < 10:
                results.append("吸烟年龄")
                other_issues.append("吸烟年龄异常")
                yellow_cells.add((idx, '吸烟情况|开始吸烟年龄'))
                score_deduction += 0.3
            if not pd.isna(quit_smoke_age) and float(quit_smoke_age) < 20:
                results.append("吸烟年龄")
                other_issues.append("戒烟年龄异常")
                yellow_cells.add((idx, '吸烟情况|戒烟年龄'))
                score_deduction += 0.3

        # 43. ============================================================================吸烟者核查（错漏，计0.5分）
        if smoking_status == '吸烟':
            start_smoke_age = row.get('吸烟情况|开始吸烟年龄')
            quit_smoke_age = row.get('吸烟情况|戒烟年龄')
            daily_smoke = row.get('吸烟情况|日吸烟量')

            if not pd.isna(start_smoke_age) and float(start_smoke_age) < 10:
                results.append("吸烟")
                other_issues.append("吸烟年龄异常")
                yellow_cells.add((idx, '吸烟情况|开始吸烟年龄'))
                score_deduction += 0.5

            if pd.isna(daily_smoke) or float(daily_smoke) <= 0:
                results.append("吸烟")
                other_issues.append("日吸烟量缺失")
                yellow_cells.add((idx, '吸烟情况|日吸烟量'))
                score_deduction += 0.5

        # 44. ============================================================================体育锻炼核查（错漏，计0.5分）
        exercise_freq = str(row.get('体育锻炼|频率', ''))
        if exercise_freq in ['每天', '每周一次以上', '偶尔']:
            minutes_per = row.get('体育锻炼|分钟/次')
            years = row.get('体育锻炼|坚持年数')
            exercise_type = row.get('体育锻炼|锻炼方式')

            if pd.isna(minutes_per) or float(minutes_per) == 0:
                results.append("锻炼")
                other_issues.append("锻炼分钟数缺失")
                yellow_cells.add((idx, '体育锻炼|分钟/次'))
                score_deduction += 0.5
            if pd.isna(years) or float(years) == 0:
                results.append("锻炼")
                other_issues.append("坚持年数缺失")
                yellow_cells.add((idx, '体育锻炼|坚持年数'))
                score_deduction += 0.5
            if pd.isna(exercise_type):
                results.append("锻炼")
                other_issues.append("锻炼方式缺失")
                yellow_cells.add((idx, '体育锻炼|锻炼方式'))
                score_deduction += 0.5

        # 45. ============================================================================饮酒情况核查（错漏，计0.5分）
        drinking_freq = str(row.get('饮酒情况|频率', ''))
        if drinking_freq in ['每天', '偶尔', '经常']:
            daily_alcohol = row.get('饮酒情况|日饮酒量')
            start_drink_age = row.get('饮酒情况|开始饮酒年龄')

            if pd.isna(daily_alcohol) or float(daily_alcohol) == 0:
                results.append("饮酒")
                other_issues.append("日饮酒量缺失")
                yellow_cells.add((idx, '饮酒情况|日饮酒量'))
                score_deduction += 0.5
            if pd.isna(start_drink_age) or float(start_drink_age) == 0:
                results.append("饮酒")
                other_issues.append("开始饮酒年龄缺失")
                yellow_cells.add((idx, '饮酒情况|开始饮酒年龄'))
                score_deduction += 0.5

        # 46. ============================================================================已戒酒者核查（错漏，计0.5分）
        if quit_drinking == '已戒酒':
            quit_drink_age = row.get('饮酒情况|戒酒年龄')
            if pd.isna(quit_drink_age) or float(quit_drink_age) < 15:
                results.append("饮酒")
                other_issues.append("戒酒年龄异常")
                yellow_cells.add((idx, '饮酒情况|戒酒年龄'))
                score_deduction += 0.5

        # 47. ============================================================================戒酒年龄逻辑核查（错漏，计0.5分）
        start_drink_age = row.get('饮酒情况|开始饮酒年龄')
        quit_drink_age = row.get('饮酒情况|戒酒年龄')
        if not pd.isna(start_drink_age) and not pd.isna(quit_drink_age):
            if float(start_drink_age) - float(quit_drink_age) < 0:
                results.append("戒酒年龄")
                other_issues.append("戒酒年龄逻辑错误")
                yellow_cells.add((idx, '饮酒情况|开始饮酒年龄'))
                yellow_cells.add((idx, '饮酒情况|戒酒年龄'))
                score_deduction += 0.5

        # 48. ============================================================================醉酒、酒种核查（错漏，计0.5分）
        if drinking_freq in ['每天', '偶尔', '经常']:
            drunk_in_year = row.get('饮酒情况|年内醉酒')
            drink_type = row.get('饮酒情况|饮酒种类')

            if pd.isna(drunk_in_year):
                results.append("醉酒")
                other_issues.append("年内醉酒缺失")
                yellow_cells.add((idx, '饮酒情况|年内醉酒'))
                score_deduction += 0.3
            if pd.isna(drink_type):
                results.append("酒种")
                other_issues.append("饮酒种类缺失")
                yellow_cells.add((idx, '饮酒情况|饮酒种类'))
                score_deduction += 0.3

        # 49. ============================================================================职业危害核查（错漏，计0.5分）
        if pd.isna(row.get('职业危害|是否接触')):
            results.append("职业危害")
            other_issues.append("职业危害缺失")
            yellow_cells.add((idx, '职业危害|是否接触'))
            score_deduction += 0.5

        # 50. ============================================================================口腔科核查（错漏，计1分）
        oral_cols = ['口腔|口唇', '口腔|咽部', '听力']
        if any(pd.isna(row.get(col)) for col in oral_cols if col in df.columns):
            results.append("口腔科")
            other_issues.append("口腔科检查缺失")
            for col in oral_cols:
                if col in df.columns and pd.isna(row.get(col)):
                    yellow_cells.add((idx, col))
            score_deduction += 1

        # 51. ============================================================================外科核查（错漏，计1分）
        surgery_cols = ['运动功能', '皮肤', '巩膜']
        if any(pd.isna(row.get(col)) for col in surgery_cols if col in df.columns):
            results.append("外科")
            other_issues.append("外科检查缺失")
            for col in surgery_cols:
                if col in df.columns and pd.isna(row.get(col)):
                    yellow_cells.add((idx, col))
            score_deduction += 1

        # 52. ============================================================================内科核查（错漏，计1分）
        internal_cols = [
            '肺|桶状胸', '肺|呼吸音', '肺|罗音', '心脏|心律', '心脏|杂音',
            '腹部|压  痛', '腹部|包  块', '腹部|肝  大', '腹部|脾  大',
            '腹部|移动性浊音', '下肢水肿', '足背动脉搏动'
        ]
        if any(pd.isna(row.get(col)) for col in internal_cols if col in df.columns):
            results.append("内科")
            other_issues.append("内科检查缺失")
            for col in internal_cols:
                if col in df.columns and pd.isna(row.get(col)):
                    yellow_cells.add((idx, col))
            score_deduction += 1

        # 53. ============================================================================血常规核查（漏，计10分）
        # 只有当血常规|血红蛋白不为空时才执行此核查
        if not skip_checks:
            blood_cols = ['血常规|血红蛋白', '血常规|白细胞', '血常规|血小板']
            if any(pd.isna(row.get(col)) for col in blood_cols if col in df.columns):
                results.append("血Rt")
                health_eval_issues.append("血常规缺失")
                for col in blood_cols:
                    if col in df.columns and pd.isna(row.get(col)):
                        yellow_cells.add((idx, col))
                score_deduction += 10

        # 54. ============================================================================尿常规核查（漏，计10分）
        # 只有当血常规|血红蛋白不为空时才执行此核查
        if not skip_checks:
            urine_cols = ['尿常规|尿蛋白', '尿常规|尿糖', '尿常规|尿酮体', '尿常规|尿潜血']
            if any(pd.isna(row.get(col)) for col in urine_cols if col in df.columns):
                results.append("尿Rt")
                health_eval_issues.append("尿常规缺失")
                for col in urine_cols:
                    if col in df.columns and pd.isna(row.get(col)):
                        yellow_cells.add((idx, col))
                score_deduction += 10

        # 55. ============================================================================肝功能核查（漏，计10分）
        # 只有当血常规|血红蛋白不为空时才执行此核查
        if not skip_checks:
            liver_cols = ['肝功能|血清谷丙转氨酶', '肝功能|血清谷草转氨酶', '肝功能|总胆红素']
            if any(pd.isna(row.get(col)) for col in liver_cols if col in df.columns):
                results.append("肝功")
                health_eval_issues.append("肝功能缺失")
                for col in liver_cols:
                    if col in df.columns and pd.isna(row.get(col)):
                        yellow_cells.add((idx, col))
                score_deduction += 10

        # 56. ============================================================================血脂核查（漏，计10分）
        # 只有当血常规|血红蛋白不为空时才执行此核查
        if not skip_checks:
            lipid_cols = [
                '血脂|总胆固醇', '血脂|甘油三酯',
                '血脂|血清低密度脂蛋白胆固醇', '血脂|血清高密度脂蛋白胆固醇'
            ]
            if any(pd.isna(row.get(col)) for col in lipid_cols if col in df.columns):
                results.append("血脂")
                health_eval_issues.append("血脂缺失")
                for col in lipid_cols:
                    if col in df.columns and pd.isna(row.get(col)):
                        yellow_cells.add((idx, col))
                score_deduction += 10

        # 57. ============================================================================肾功能核查（漏，计10分）
        # 只有当血常规|血红蛋白不为空时才执行此核查
        if not skip_checks:
            renal_cols = ['肾功能|血清肌酐', '肾功能|血 尿 素']
            if any(pd.isna(row.get(col)) for col in renal_cols if col in df.columns):
                results.append("肾功")
                health_eval_issues.append("肾功能缺失")
                for col in renal_cols:
                    if col in df.columns and pd.isna(row.get(col)):
                        yellow_cells.add((idx, col))
                score_deduction += 10

        # 58. ============================================================================检查项核查（漏，计10分）
        # 只有当血常规|血红蛋白不为空时才执行此核查
        if not skip_checks:
            exam_cols = ['心电图', '腹部B超']
            if any(pd.isna(row.get(col)) for col in exam_cols if col in df.columns):
                results.append("检查")
                health_eval_issues.append("检查项缺失")
                for col in exam_cols:
                    if col in df.columns and pd.isna(row.get(col)):
                        yellow_cells.add((idx, col))
                score_deduction += 10

        # 59. ============================================================================其他系统疾病核查（漏，计1分）
        other_disease = row.get('其他系统疾病')
        other_disease_other = row.get('其他系统疾病其它')
        if other_disease == '有' and pd.isna(other_disease_other):
            results.append("既往史")
            health_problem_issues.append("其他系统疾病缺失")
            yellow_cells.add((idx, '其他系统疾病'))
            score_deduction += 1

        # 60-61. 标签核查（错漏，计0.1分）

        # 62-64. ==========================================================================血压评价核查（错漏，计1分）
        def contains_keyword_in_disease(keyword):
            return any(keyword in str(row.get(col, '')) for col in disease_cols if col in df.columns)

        ls = row.get('左收缩压', 0)
        ld = row.get('左舒张压', 0)
        rs = row.get('右收缩压', 0)
        rd = row.get('右舒张压', 0)

        if age is not None:
            if age >= 65:
                if (ls >= 150 or rs >= 150 or ld >= 90 or rd >= 90) and not contains_keyword_in_disease('血压'):
                    results.append("少血压评价")
                    health_eval_issues.append("少血压评价")
                    if '异常疾病|异常1' in df.columns:
                        yellow_cells.add((idx, '异常疾病|异常1'))
                    score_deduction += 1
            else:  # age < 65
                if (ls >= 140 or rs >= 140 or ld >= 90 or rd >= 90) and not contains_keyword_in_disease('血压'):
                    results.append("少血压评价")
                    health_eval_issues.append("少血压评价")
                    if '异常疾病|异常1' in df.columns:
                        yellow_cells.add((idx, '异常疾病|异常1'))
                    score_deduction += 1

        # 65-66. ==========================================================================腹型肥胖核查评价情况，分男女（错漏，计1分）
        waist_val = row.get('腰围', 0)
        gender = row.get('性别')

        def contains_obesity_keyword():
            keywords = ['腹型肥胖', '腰围', '性肥胖']
            return any(any(kw in str(row.get(col, '')) for kw in keywords)
                       for col in disease_cols if col in df.columns)

        if gender == '女' and waist_val >= 85 and not contains_obesity_keyword():
            results.append("少腹型肥胖")
            health_eval_issues.append("少腹型肥胖评价")
            if '异常疾病|异常1' in df.columns:
                yellow_cells.add((idx, '异常疾病|异常1'))
                yellow_cells.add((idx, '腰围'))
            score_deduction += 1

        if gender == '男' and waist_val >= 90 and not contains_obesity_keyword():
            results.append("少腹型肥胖")
            health_eval_issues.append("少腹型肥胖评价")
            if '异常疾病|异常1' in df.columns:
                yellow_cells.add((idx, '异常疾病|异常1'))
                yellow_cells.add((idx, '腰围'))
            score_deduction += 1
                     
        # 67. ===== 新增功能：危险因素控制中的"减腰围"核查2025.06.18 ===========================（错漏，计1分）
        risk_factors = row.get('危险因素控制', '')
        risk_factors_two = row.get('其他危险因素', '')

        # 处理NaN和空字符串的统一判断
        is_risk_factors_empty = pd.isna(risk_factors) or str(risk_factors).strip() == ''
        is_risk_factors_two_empty = pd.isna(risk_factors_two) or str(risk_factors_two).strip() == ''

        if is_risk_factors_empty and is_risk_factors_two_empty:
            results.append("危险因素控制空")
            health_guide_issues.append("危险因素控制空")
            yellow_cells.add((idx, '危险因素控制'))
            score_deduction += 0.5
        
        #-----------------------------------------------------------------------------上段用于判断危险因素控制是否为空
        
        risk_factors = str(row.get('危险因素控制', '')) 
        risk_factors_two = str(row.get('其他危险因素', ''))
        gender = row.get('性别')
        waist_val = row.get('腰围', 0)

        if not pd.isna(gender) and not pd.isna(waist_val):
            # 男性规则
            if gender == '男':
                if waist_val >= 90:
                    if ("减腰围" not in risk_factors) and ("减腰围" not in risk_factors_two):
                        results.append("少减腰围")
                        health_guide_issues.append("少减腰围建议")
                        yellow_cells.add((idx, '危险因素控制'))
                        yellow_cells.add((idx, '其他危险因素'))
                        score_deduction += 1
                else:  # 腰围 < 90
                    if ("减腰围" in risk_factors) or ("减腰围" in risk_factors_two):
                        results.append("多减腰围")
                        health_guide_issues.append("多减腰围建议")
                        yellow_cells.add((idx, '危险因素控制'))
                        yellow_cells.add((idx, '其他危险因素'))
                        score_deduction += 1
            
            # 女性规则
            elif gender == '女':
                if waist_val >= 85:
                    if ("减腰围" not in risk_factors) and ("减腰围" not in risk_factors_two):
                        results.append("少减腰围")
                        health_guide_issues.append("少减腰围建议")
                        yellow_cells.add((idx, '危险因素控制'))
                        yellow_cells.add((idx, '其他危险因素'))
                        score_deduction += 1
                else:  # 腰围 < 85
                    if ("减腰围" in risk_factors) or ("减腰围" in risk_factors_two):
                        results.append("多减腰围")
                        health_guide_issues.append("多减腰围建议")
                        yellow_cells.add((idx, '危险因素控制'))
                        yellow_cells.add((idx, '其他危险因素'))
                        score_deduction += 1

        # 68. ==========================================================================体重评价核查（错漏，计1分）
        bmi_val = row.get('体质指数')

        if not pd.isna(bmi_val):
            # 检查是否包含体重相关关键词
            def contains_weight_keyword(keywords):
                return any(any(kw in str(row.get(col, '')) for kw in keywords)
                           for col in disease_cols if col in df.columns)

            if bmi_val < 18.5:
                if not contains_weight_keyword(['体重', '消瘦']):
                    results.append("少体指评价")
                    health_eval_issues.append("体重偏轻未评价")
                    if '异常疾病|异常1' in df.columns:
                        yellow_cells.add((idx, '异常疾病|异常1'))
                    score_deduction += 1

            elif 24 <= bmi_val < 28:
                if not contains_weight_keyword(['超重', '偏胖']):
                    results.append("少体指评价")
                    health_eval_issues.append("超重未评价")
                    if '异常疾病|异常1' in df.columns:
                        yellow_cells.add((idx, '异常疾病|异常1'))
                    score_deduction += 1
            elif bmi_val >= 28:
                if not contains_weight_keyword(['肥胖']):
                    results.append("少体指评价")
                    health_eval_issues.append("肥胖未评价")
                    if '异常疾病|异常1' in df.columns:
                        yellow_cells.add((idx, '异常疾病|异常1'))
                    score_deduction += 1

        # 69. ===== 新增功能：血糖多评价规则 ========================================（漏，计1分）
        fasting_bs = row.get('空腹血糖')
        if not pd.isna(fasting_bs) and 3.9 <= fasting_bs < 6.1:
            # 检查是否包含特定血糖关键词
            contains_blood_sugar = any(
                keyword in str(row.get(col, '')) 
                for keyword in ['血糖偏高', '血糖控制不满意']
                for col in disease_cols if col in df.columns
            )
            
            if contains_blood_sugar:
                results.append("血糖错评价")
                health_eval_issues.append("血糖错评价")
                for col in disease_cols:
                    if col in df.columns:
                        yellow_cells.add((idx, col))
                score_deduction += 1
        
        # 70. ========新增：指导空核查=============================================（空：0.5分）
        health_guide = str(row.get('危险因素控制', ''))
        if pd.isna(health_guide):
                results.append("指导空")
                health_guide_issues.append("指导空")
                yellow_cells.add((idx, '危险因素控制'))
                score_deduction += 0.5

        # 根据积分分值设置体检表是否规范，积分分值>=-1 表示扣分不超过1分，视为规范
        if score_deduction < 1:  # 积分分值 = -score_deduction
            df.at[idx, '体检表是否规范'] = '是'
        else:
            df.at[idx, '体检表是否规范'] = '否'

        # ===== 更新列数据 =====
        df.at[idx, '积分分值'] = -score_deduction  # 积分为扣分的负值
        df.at[idx, '核查结果汇总'] = ', '.join(set(results))
        df.at[idx, '核查现存健康问题'] = ', '.join(set(health_problem_issues))
        df.at[idx, '核查健康评价'] = ', '.join(set(health_eval_issues))
        df.at[idx, '核查健康指导'] = ', '.join(set(health_guide_issues))
        df.at[idx, '核查其他空错项'] = ', '.join(set(other_issues))
        
    return df, yellow_cells

def process_physical_exam():
    """处理体检表函数（单表版）"""
    try:
        # 创建输出目录
        output_dir = "临时_核查结果"
        os.makedirs(output_dir, exist_ok=True)

        # 步骤1: 读取体检数据
        # print_step(1, "读取体检数据")
        dtype_dict = {'个人健康档案号': str, '身份证号': str}
        df = pd.read_excel("体检明细表.xlsx", dtype=dtype_dict)
        df['体检日期'] = pd.to_datetime(df['体检日期'], errors='coerce').dt.strftime('%Y-%m-%d')
        record_count = len(df)

        # 步骤2: 核查数据
        df, yellow_cells = check_physical_exam_data(df)

        # 步骤3: 保存结果
        print_step(2, "保存核查结果")
        output_path = os.path.join(output_dir, '体检明细表_已核查.xlsx')
        
        with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
            df.to_excel(writer, index=False, sheet_name='体检明细表')
            
            # 获取工作簿和工作表
            workbook = writer.book
            worksheet = writer.sheets['体检明细表']
            
            # 设置格式
            for row_idx in range(2, len(df) + 2):
                cell = worksheet.cell(row=row_idx, column=df.columns.get_loc('个人健康档案号') + 1)
                cell.number_format = '@'
            
            # 标记黄色单元格
            yellow_fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
            for (idx, col_name) in yellow_cells:
                col_idx = df.columns.get_loc(col_name) + 1
                row_idx = idx + 2
                worksheet.cell(row=row_idx, column=col_idx).fill = yellow_fill

        return True, record_count

    except Exception as e:
        print_error(f"处理过程中发生错误: {str(e)}")
        import traceback
        traceback.print_exc()
        return False, 0

def main():
    """主函数"""
    try:
        print_header("【体检表核查】 - 2025.08.07")
        print("功能说明:\n  1. 单表核查模式\n  2. 使用极值判断规则")
        print('-' * 50)
        start_time = time.time()
        success, record_count = process_physical_exam()
        elapsed_time = time.time() - start_time

        if success:
            print_success("下一步...")
        else:
            print_error("任务未能完成，请检查错误信息")

    except Exception as e:
        print_error(f"程序发生未预期的错误: {str(e)}")

if __name__ == "__main__":
    main()
####################################################################################################
##此段代码为随访表的核查
####################################################################################################
import pandas as pd
import numpy as np
import re
import os
from tqdm import tqdm
import time
import datetime
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Font, colors
import warnings

# 忽略FutureWarning
warnings.simplefilter(action='ignore', category=FutureWarning)

def process_hypertension_followup():
    output_dir = "临时_核查结果"
    os.makedirs(output_dir, exist_ok=True)

    print("步骤1: 读取糖尿病随访明细表...")
    df = pd.read_excel("糖尿病随访明细.xlsx", dtype={'身份证号': str})

    df['随访日期'] = pd.to_datetime(df['随访日期'], errors='coerce')

    # 添加新列
    df['季度'] = ""
    df['季度内随访次序'] = ""
    df['核查结果'] = ""
    df['完整性'] = ""

    # 步骤2: 计算季度和随访次数
    print("步骤2: 计算季度和季度内随访次序...")
    # 提取年份
    df['年份'] = df['随访日期'].dt.year

    # 按身份证号和年份分组
    grouped = df.groupby(['身份证号', '年份'])

    # 为每个分组计算季度和随访次数
    for (id_card, year), group in tqdm(grouped, desc="  计算季度和季度内随访次序"):
        # 按随访日期排序
        group = group.sort_values('随访日期')

        # 计算季度
        group['季度'] = group['随访日期'].dt.quarter

        # 计算季度内的随访次数
        group['季度内随访次序'] = group.groupby('季度').cumcount() + 1

        # 更新原始DataFrame
        df.loc[group.index, '季度'] = group['季度']
        df.loc[group.index, '季度内随访次序'] = group['季度内随访次序']

    # 按身份证号和年份分组
    grouped = df.groupby(['身份证号', '年份'])

    # 步骤6: 进行数据核查
    print("步骤6: 进行随访核查，请等待...")
    # 初始化核查结果列
    df['核查结果'] = ""

    # 对每一行进行核查
    for idx, row in tqdm(df.iterrows(), total=len(df), desc="  数据核查"):
        errors = []

        # 1.下次随访日期校验
        try:
            follow_date = row['随访日期'].date() if isinstance(row['随访日期'], pd.Timestamp) else pd.to_datetime(
                row['随访日期']).date()
            next_date = row['下次随访日期'].date() if isinstance(row['下次随访日期'], pd.Timestamp) else pd.to_datetime(
                row['下次随访日期']).date()
            delta = (next_date - follow_date).days
            if delta <= 6 or delta > 94:
                errors.append("下次随访日期有误；")
        except:
            pass

        # 2.随访方式校验
        if row['随访方式'] not in ['家庭', '门诊']:
            errors.append("随访方式有误；")

        # 3.血压校验
        k = row['收缩压']
        l = row['舒张压']
        am = row['转诊原因']
        if pd.notnull(k):
            if k < 70 or k >= 240:
                errors.append("收缩压有误；")
        if pd.notnull(l):
            if l <= 40 or l >= 140:
                errors.append("舒张压有误；")

        # 4.体重校验
        m_val = row['体重']
        if pd.notnull(m_val):
            if m_val > 150 or m_val < 30:
                errors.append("体重有误；")

        # 5.目标体重校验
        n_val = row['目标体重']
        p_val = row['体质指数']
        if pd.notnull(n_val) and pd.notnull(p_val):
            if p_val >= 24:
                if n_val >= row['体重']:
                    errors.append("目标体重有误；")
            elif p_val < 18.5:
                if n_val <= row['体重']:
                    errors.append("目标体重有误；")
            else:
                if n_val != row['体重']:
                    errors.append("目标体重有误；")
        else:
            errors.append("目标体重为空；")

        # 6.身高校验
        o_val = row['身高']
        if pd.notnull(o_val) and (o_val > 190 or o_val < 90):
            errors.append("身高有误；")

        # 7.足背动脉校验
        r_val = row['足背动脉搏动']
        if pd.isnull(r_val) :
            errors.append("足背动脉有误；")

        # 8.吸烟量校验
        t_val = row['日吸烟量']
        if not (isinstance(t_val, (int, float)) and float(t_val).is_integer()):
            errors.append("吸烟量有误；")

        # 9.目标吸烟校验
        u_val = row['目标吸烟量']
        if pd.notnull(u_val) and pd.notnull(t_val) and u_val > t_val:
            errors.append("目标吸烟有误；")

        # 10.饮酒量校验
        v_val = row['饮酒量']
        if not isinstance(v_val, (int, float)):
            errors.append("饮酒量有误；")

        # 11.目标饮酒校验
        w_val = row['目标饮酒量']
        if pd.notnull(w_val) and pd.notnull(v_val) and w_val > v_val:
            errors.append("目标饮酒有误；")

        # 12.运动次校验
        x_val = row['周运动量']
        if not (isinstance(x_val, (int, float)) and float(x_val).is_integer()):
            errors.append("运动次有误；")

        # 13.运动量校验
        y_val = row['每次运动时间']
        if not (isinstance(y_val, (int, float)) and float(y_val).is_integer()):
            errors.append("运动量有误；")

        # 14.运动次数目标校验
        z_val = row['目标运动量']
        if pd.notnull(x_val) and pd.notnull(z_val):
            if x_val >= 5:
                if z_val not in [5, 6, 7]:
                    errors.append("目标运动次有误；")
            else:
                if (z_val - x_val) > 4:
                    errors.append("目标运动次有误；")

        # 15.运动时长目标校验
        aa_val = row['目标运动时间']
        if pd.notnull(y_val) and pd.notnull(aa_val):
            if y_val >= 60:
                if (aa_val - y_val) > 0:
                    errors.append("目标运动时长有误；")
            else:
                if (aa_val - y_val) > 30:
                    errors.append("目标运动时长有误；")

        # 16.主食量与目标饮食
        staple_food = row['主食量']
        target_staple_food = row['目标主食量']
        if pd.notnull(staple_food) and pd.notnull(target_staple_food) :
            if staple_food > 700 or staple_food < 100:
                errors.append("主食量有误；")
            elif (staple_food > 250) and ( (staple_food - target_staple_food) < 0):
                errors.append("目标主食量有误；")
        else:
            errors.append("主食量或目标量为空；")
        
        # 17 血糖校验
        fasting_glucose = row['空腹血糖']
        if pd.notnull(fasting_glucose):
            if (fasting_glucose > 35 or fasting_glucose < 2) :
                errors.append("血糖值有误；")

        # 17.依从性校验
        ag_val = row['胰岛素种类']
        agt_val = row['用药情况']
        ah_val = str(row['服药依从性']) if pd.notnull(row['服药依从性']) else ""
        if pd.isnull(ag_val) and pd.isnull(agt_val) and ah_val in ["规律","间断"]:
            errors.append("依从性有误；")
        elif ag_val in ["无"]  and pd.isnull(agt_val) and ah_val == "规律":
            errors.append("依从性有误；")
        elif ag_val in ["无"] and pd.isnull(agt_val) and ah_val == "间断":
            errors.append("依从性有误；")
        elif pd.notna(ag_val)  and pd.isnull(agt_val) and "不" in str(ag_val) and ah_val in ["规律","间断"]:
            errors.append("依从性有误；")
        elif pd.notna(ag_val) and pd.isnull(agt_val) and "未" in str(ag_val) and ah_val in ["规律","间断"]:
            errors.append("依从性有误；")
        elif pd.notna(ag_val) and pd.isnull(agt_val) and "未" not in str(ag_val) and "不" not in str(ag_val) and "无" not in str(ag_val)  and ah_val in ["不服药"]:
            errors.append("依从性有误；")

        # 18.随访分类校验
        followup_class = row['随访分类']
        if pd.notnull(fasting_glucose) and pd.notnull(followup_class):
            if 3.9 <= fasting_glucose < 7.0:
                if followup_class != "控制满意":
                    errors.append("随访分类有误；")
            else:
                if (followup_class != "控制不满意") and (followup_class != "不良反应" ):
                    errors.append("随访分类有误；")

        # 19.转诊校验
        referral = row['转诊原因']
        referral_org = row['转诊机构']
        if pd.notnull(referral) and pd.isnull(referral_org):
            errors.append("转诊机构缺失；")
        if pd.isnull(referral) and pd.notnull(referral_org):
            errors.append("转诊原因缺失；")

        # 20.随访医生校验
        ap_val = row['随访医生签名']
        if pd.isnull(ap_val):
            errors.append("随访医生空；")

        # 将错误信息写入核查结果列
        df.at[idx, '核查结果'] = "; ".join(errors)
        df.at[idx, '完整性'] = '是' if not errors else '否'

    # 步骤7: 保存结果
    print("步骤7: 正在保存结果...")
    output_path = os.path.join(output_dir, '糖尿病随访表_已标记.xlsx')

    df['随访日期'] = df['随访日期'].dt.strftime('%Y-%m-%d')

    print("步骤8: 重新进行数据排序...")
    df = df.sort_values(by=['身份证号', '随访日期'], ascending=[True, True])

    # 保存到Excel
    with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
        df.to_excel(writer, index=False)

        # 获取工作簿和工作表
        workbook = writer.book
        worksheet = writer.sheets['Sheet1']

        # 设置标题格式
        title_font = Font(bold=True, color=colors.BLUE)
        for col in range(1, len(df.columns) + 1):
            cell = worksheet.cell(row=1, column=col)
            cell.font = title_font

        # 确保身份证号为文本格式
        if '身份证号' in df.columns:
            id_col_idx = df.columns.get_loc('身份证号') + 1
            for row in range(2, len(df) + 2):
                cell = worksheet.cell(row=row, column=id_col_idx)
                cell.number_format = '@'
                if cell.value is not None:
                    cell.value = str(cell.value)

        # 确保随访日期格式正确
        if '随访日期' in df.columns:
            date_col_idx = df.columns.get_loc('随访日期') + 1
            for row in range(2, len(df) + 2):
                cell = worksheet.cell(row=row, column=date_col_idx)
                if cell.value:
                    # 应用日期格式
                    cell.number_format = 'yyyy-mm-dd'

if __name__ == "__main__":
    print("=" * 60)
    print("【糖尿病随访明细表核查】-2025.08.09...")
    print("功能说明:")
    print("  1. 按身份证号和随访日期计算季度和季度内随访次序")
    print("  2. 对每次随访数据进行核查，并判断随访是否合格")
    print("-" * 50)

    start_time = time.time()
    process_hypertension_followup()
    elapsed_time = time.time() - start_time

    print(f"耗时: {elapsed_time:.2f}秒...")

####################################################################################################
##此段代码为三史一致性核查2
####################################################################################################
import pandas as pd
import numpy as np
from tqdm import tqdm
import os

# 设置路径和文件
base_dir = os.getcwd()
followup_path = os.path.join(base_dir, '糖尿病随访明细.xlsx')
resident_path = os.path.join(base_dir, '居民档案.xlsx')
physical_exam_path = os.path.join(base_dir, '临时_核查结果', '体检明细表_已核查.xlsx')
output_path = os.path.join(base_dir, '临时_核查结果', '糖尿病_三史一致性.xlsx')

# 创建输出目录（如果不存在）
os.makedirs(os.path.dirname(output_path), exist_ok=True)

# 1. 加载糖尿病随访数据（只保留唯一身份证号）
print("=" * 60)
print("【三史一致性核查】...2025.08.05...")
print("功能说明:")
print("  1. 提取所需数据")
print("  2. 双向判断是否一致")
print("  3. 主要核查高血压、糖尿病、肿瘤一致性")
print("  4. 慢阻肺暂不考虑，代码保留")
print("-" * 50)
print("步骤1: 加载糖尿病随访数据...")
df_followup = pd.read_excel(followup_path)
# 保留每个身份证号的第一条记录
df_base = df_followup.drop_duplicates(subset='身份证号', keep='first')[['所属管辖', '姓名', '身份证号']].copy()
df_base['是否是在管糖尿病'] = '是'

# 2. 加载体检数据
print("步骤2: 加载体检数据...")
df_physical = pd.read_excel(physical_exam_path)
df_physical = df_physical.drop_duplicates('身份证号', keep='first').rename(
    columns={'体检日期': '体检标签', '其他系统疾病其它': '体检表现存主要健康问题'}
)[['身份证号', '体检标签', '体检表现存主要健康问题']]

df_physical[[ '体检标签', '体检表现存主要健康问题']] = df_physical[
    ['体检标签', '体检表现存主要健康问题']].fillna('').astype(str)

# 3. 加载居民档案数据
print("步骤3: 加载居民档案数据...")
df_resident = pd.read_excel(resident_path).drop_duplicates('身份证号', keep='first')
df_resident['居民档案疾病史'] = df_resident['疾病史'].fillna('').astype(str)
df_resident = df_resident[['身份证号', '居民档案疾病史']]

# 4. 合并数据
print("步骤4: 合并数据集...")
df_result = pd.merge(
    df_base, 
    df_physical, 
    on='身份证号', 
    how='left'
).merge(
    df_resident, 
    on='身份证号', 
    how='left'
)

# 处理缺失值
df_result['是否体检'] = df_result['体检标签'].apply(lambda x: '是' if not pd.isna(x) else '否')

for col in ['体检标签', '体检表现存主要健康问题', '居民档案疾病史']:
    df_result[col] = df_result[col].fillna('').astype(str)

# 5. 核查一致性（带进度条）
print("步骤5: 执行核查逻辑...")
tqdm.pandas(desc="  进度")
def check_row(row):
    reasons = []
    
    if (row['是否体检'] == '是') and ('糖' not in row['体检表现存主要健康问题']):
        reasons.append('现存主要健康问题无糖尿病')
    if '糖' not in row['居民档案疾病史']:
        reasons.append('档案疾病史无糖尿病')
    
    # 条件检查（仅在体检的情况下）
    if row['是否体检'] == '是':
        if '糖' in row['体检表现存主要健康问题'] and '糖' not in row['居民档案疾病史']:
            reasons.append('体检表与档案不一致')

        if ('瘤' in row['体检表现存主要健康问题'] or '癌' in row['体检表现存主要健康问题']) and \
           ('瘤' not in row['居民档案疾病史'] and '癌' not in row['居民档案疾病史']):
            reasons.append('体检表与档案不一致')
        
        if '糖' in row['居民档案疾病史'] and '糖' not in row['体检表现存主要健康问题']:
            reasons.append('体检表与档案不一致')

        if ('瘤' in row['居民档案疾病史'] or '癌' in row['居民档案疾病史']) and \
           ('瘤' not in row['体检表现存主要健康问题'] and '癌' not in row['体检表现存主要健康问题']):
            reasons.append('体检表与档案不一致')

    reason_str = ';'.join(sorted(set(reasons))) if reasons else ''
    
    consistent = '是' if reason_str == '' else '否'
    return pd.Series([consistent, reason_str])

# 应用核查函数
df_result[['是否一致', '不一致原因']] = df_result.progress_apply(check_row, axis=1)

# 6. 保存结果
df_result[
    ['所属管辖', '姓名', '身份证号', '是否是在管糖尿病', '是否体检', 
     '体检标签', '体检表现存主要健康问题', '居民档案疾病史',
     '是否一致', '不一致原因']
].to_excel(output_path, index=False)
print("-" *50)
print("共处理 {} 条记录".format(len(df_result)))

####################################################################################################
##此段代码为同天随访，体检数据核对
####################################################################################################
import pandas as pd
import numpy as np
from tqdm import tqdm
import os

# 设置显示选项（可选）
pd.set_option('display.max_columns', None)

def compare_values(value1, value2):
    """比较两个值是否一致（处理空值和数值类型）"""
    if pd.isna(value1) or pd.isna(value2):
        return False
    try:
        # 数值类型比较容差
        return abs(float(value1) - float(value2)) < 1e-5
    except:
        # 非数值类型直接比较
        return value1 == value2

def main():
    # 读取数据
    print("=" * 60)
    print("【同天随访体检核查】-2025.08.09...")
    print("功能说明:")
    print("  1. 提取所需数据")
    print("  2. 判断常规项是否一致")
    print("  3. 主要核查身高、体重、血压、生活方式、血糖一致性")
    print("-" * 50)
    print("步骤1: 重新读取随访数据...")
    follow_df = pd.read_excel('糖尿病随访明细.xlsx')
    
    print("步骤2: 正在读取体检数据...")
    physical_df = pd.read_excel('临时_核查结果/体检明细表_已核查.xlsx')

    # 预处理：日期字段标准化
    for df in [follow_df, physical_df]:
        df['随访日期'] = pd.to_datetime(df['随访日期']).dt.date if '随访日期' in df.columns else None
        df['体检日期'] = pd.to_datetime(df['体检日期']).dt.date if '体检日期' in df.columns else None

    # 创建结果DataFrame框架
    result_columns = [
        '所属管辖', '姓名', '身份证号', '是否同天体检随访', '随访日期', '体检日期', '体检类型',
        '随_收缩压', '随_舒张压', '随_体重', '随_身高', '随_血糖', '随_吸烟量', '随_饮酒量', '随_运动量',
        '体_左收缩压', '体_左舒张压', '体_右收缩压', '体_右舒张压', '体_体重', '体_身高', '体_血糖', 
        '体_吸烟量', '体_饮酒量', '体_是否戒酒', '体_运动量', '是否一致', '不一致原因'
    ]
    results = []
    
    # 提取唯一身份证号列表
    unique_ids = follow_df['身份证号'].unique()
    print(f"  发现 {len(unique_ids)} 个在管糖尿病...")

    # 创建体检数据索引（加速查询）
    physical_index = {}
    for _, row in physical_df.iterrows():
        id_card = row['身份证号']
        exam_date = row['体检日期']
        if pd.notna(id_card) and pd.notna(exam_date):
            if id_card not in physical_index:
                physical_index[id_card] = {}
            physical_index[id_card][exam_date] = row.to_dict()

    # 处理每个随访对象
    print("步骤3: 开始处理记录...")
    for id_card in tqdm(unique_ids, desc="  进度"):
        # 获取当前身份证号的所有随访记录
        follow_records = follow_df[follow_df['身份证号'] == id_card]
        if len(follow_records) == 0:
            continue
            
        # 默认使用第一条随访记录的基础信息
        base_info = follow_records.iloc[0]
        result_row = {
            '所属管辖': base_info['所属管辖'],
            '姓名': base_info['姓名'],
            '身份证号': id_card,
            '是否同天体检随访': '否',
            '是否一致': None,
            '不一致原因': None
        }
        
        # 初始化所有字段为None
        for col in result_columns[3:]:
            if col not in ['是否同天体检随访', '是否一致', '不一致原因']:
                result_row[col] = None
        
        # 检查是否存在同天体检记录
        if id_card in physical_index:
            for _, follow_row in follow_records.iterrows():
                follow_date = follow_row['随访日期']
                if follow_date in physical_index[id_card]:
                    physical_data = physical_index[id_card][follow_date]
                    
                    # 标记同天随访体检
                    result_row['是否同天体检随访'] = '是'
                    result_row['随访日期'] = follow_date
                    result_row['体检日期'] = follow_date
                    
                    # 确定体检类型
                    result_row['体检类型'] = '公卫' if pd.notna(physical_data.get('血常规|血红蛋白', None)) else '其他'
                    
                    # 填充随访数据
                    follow_mapping = {
                        '随_收缩压': '收缩压',
                        '随_舒张压': '舒张压',
                        '随_体重': '体重',
                        '随_身高': '身高',
                        '随_血糖': '空腹血糖',
                        '随_吸烟量': '日吸烟量',
                        '随_饮酒量': '饮酒量',
                        '随_运动量': '周运动量'
                    }
                    for result_col, follow_col in follow_mapping.items():
                        result_row[result_col] = follow_row.get(follow_col)
                    
                    # 填充体检数据
                    physical_mapping = {
                        '体_左收缩压': '左收缩压',
                        '体_左舒张压': '左舒张压',
                        '体_右收缩压': '右收缩压',
                        '体_右舒张压': '右舒张压',
                        '体_体重': '体重',
                        '体_身高': '身高',
                        '体_血糖': '空腹血糖',
                        '体_吸烟量': '吸烟情况|状况',
                        '体_饮酒量': '饮酒情况|频率',
                        '体_是否戒酒': '饮酒情况|是否戒酒',
                        '体_运动量': '体育锻炼|频率'
                    }
                    for result_col, physical_col in physical_mapping.items():
                        result_row[result_col] = physical_data.get(physical_col)
                    
                    # 一致性检查
                    inconsistencies = []
                    
                    # 1. 血压检查
                    bp_match = (
                        (compare_values(result_row['随_收缩压'], result_row['体_左收缩压']) and 
                         compare_values(result_row['随_舒张压'], result_row['体_左舒张压'])) or
                        (compare_values(result_row['随_收缩压'], result_row['体_右收缩压']) and 
                         compare_values(result_row['随_舒张压'], result_row['体_右舒张压']))
                    )
                    if not bp_match:
                        inconsistencies.append('血压')
                    
                    # 2. 体重检查
                    if not compare_values(result_row['随_体重'], result_row['体_体重']):
                        inconsistencies.append('体重')
                    
                    # 3. 身高检查
                    if not compare_values(result_row['随_身高'], result_row['体_身高']):
                        inconsistencies.append('身高')
                    
                    # 4. 心率检查
                    if not compare_values(result_row['随_血糖'], result_row['体_血糖']):
                        inconsistencies.append('血糖')
                    
                    # 5. 吸烟量检查
                    follow_smoke = result_row['随_吸烟量']
                    physical_smoke = result_row['体_吸烟量']
                    if (follow_smoke == 0 and physical_smoke == '吸烟') or \
                       (follow_smoke != 0 and physical_smoke in ['从不吸烟', '已戒烟']):
                        inconsistencies.append('吸烟')
                    
                    # 6. 饮酒量检查
                    follow_drink = result_row['随_饮酒量']
                    physical_drink = result_row['体_饮酒量']
                    physical_quit = result_row['体_是否戒酒']
                    if (follow_drink == 0 and physical_drink in ['每天', '经常', '偶尔'] and physical_quit == '未戒酒') or \
                       (follow_drink != 0 and (physical_drink == '从不' or physical_quit == '已戒酒')):
                        inconsistencies.append('饮酒')
                    
                    # 7. 运动量检查
                    follow_exercise = result_row['随_运动量']
                    physical_exercise = result_row['体_运动量']
                    if (follow_exercise == 0 and physical_exercise in ['每天', '每周一次以上']) or \
                       (follow_exercise != 0 and physical_exercise == '不锻炼'):
                        inconsistencies.append('锻炼')
                    
                    # 更新一致性结果
                    result_row['是否一致'] = '是' if not inconsistencies else '否'
                    result_row['不一致原因'] = ';'.join(inconsistencies) if inconsistencies else None
                    
                    break  # 找到第一个匹配的就停止
                    
        results.append(result_row)
    
    # 创建结果DataFrame并保存
    result_df = pd.DataFrame(results, columns=result_columns)
    output_path = '临时_核查结果/糖尿病_同天随访体检.xlsx'
    
    # 确保输出目录存在
    os.makedirs(os.path.dirname(output_path), exist_ok=True)
    
    result_df.to_excel(output_path, index=False)
    print("-" * 50)
    print(f"同天随访体检核查完成！共处理 {len(results)} 条记录")
    
if __name__ == "__main__":
    main()
####################################################################################################
##此段代码为随访，体检表全年数据纵横比较
####################################################################################################
import pandas as pd
import numpy as np
import os
from tqdm import tqdm
import warnings

warnings.filterwarnings("ignore", category=FutureWarning)

def check_height(row):
    """核查身高一致性并返回扣分值和说明"""
    exam_val = row.get('体_身高')
    max_val = row.get('随_最大身高')
    min_val = row.get('随_最小身高')
    
    f_int = 0.0
    issue_list = []
    
    # 跳过空值比较
    if pd.isna(exam_val) or pd.isna(max_val) or pd.isna(min_val):
        return f_int, ''
    
    # 检查三种差异
    diff1 = abs(max_val - exam_val)
    diff2 = abs(min_val - exam_val)
    diff3 = abs(max_val - min_val)
    
    if diff1 >= 3 or diff2 >= 3 or diff3 >= 3:
        f_int -= 1.0
        issue_list.append('身高差>=3')
    
    return f_int, ';'.join(issue_list)

def check_weight(row):
    """核查体重一致性并返回扣分值和说明"""
    exam_val = row.get('体_体重')
    max_val = row.get('随_最大体重')
    min_val = row.get('随_最小体重')
    
    f_int = 0.0
    issue_list = []
    
    if pd.isna(exam_val) or pd.isna(max_val) or pd.isna(min_val):
        return f_int, ''
    
    diff1 = abs(max_val - exam_val)
    diff2 = abs(min_val - exam_val)
    diff3 = abs(max_val - min_val)
    
    if diff1 >= 8 or diff2 >= 8 or diff3 >= 8:
        f_int -= 1.0
        issue_list.append('体重差>=8')
    
    return f_int, ';'.join(issue_list)

def check_smoke(row):
    """核查吸烟量一致性并返回扣分值和说明"""
    exam_val = row.get('体_吸烟量', '')
    max_val = row.get('随_最大吸烟量')
    min_val = row.get('随_最小吸烟量')
    
    f_int = 0.0
    issue_list = []
    
    if pd.isna(max_val) or pd.isna(min_val):
        return f_int, ''
    
    # 条件1：随访不吸烟 vs 体检显示吸烟
    if max_val == 0 and min_val == 0 and exam_val == '吸烟':
        f_int -= 0.5
        issue_list.append('吸烟差异')
    
    # 条件2：随访有吸烟 vs 体检从不/已戒烟
    if max_val > 0 and min_val > 0 and exam_val in ['从不吸烟', '已戒烟']:
        f_int -= 0.5
        issue_list.append('吸烟差异')
    
    # 条件3：随访吸烟量差异过大
    if abs(max_val - min_val) >= 20:
        f_int -= 0.5
        issue_list.append('吸烟差>=20')
    
    return f_int, ';'.join(issue_list)

def check_alcohol(row):
    """核查饮酒量一致性并返回扣分值和说明"""
    exam_val = row.get('体_饮酒量', '')
    max_val = row.get('随_最大饮酒量')
    min_val = row.get('随_最小饮酒量')
    
    f_int = 0.0
    issue_list = []
    
    if pd.isna(max_val) or pd.isna(min_val):
        return f_int, ''
    
    # 条件1：随访不饮酒 vs 体检显示常饮
    if max_val == 0 and min_val == 0 and exam_val in ['每天', '经常']:
        f_int -= 0.5
        issue_list.append('饮酒差异')
    
    # 条件2：随访有饮酒 vs 体检从不饮酒
    if max_val > 0 and min_val > 0 and exam_val == '从不':
        f_int -= 0.5
        issue_list.append('饮酒差异')
    
    # 条件3：随访饮酒量差异过大
    if abs(max_val - min_val) >= 4:
        f_int -= 0.5
        issue_list.append('饮酒差>=4')
    
    return f_int, ';'.join(issue_list)

def check_exercise(row):
    """核查运动量一致性并返回扣分值和说明"""
    exam_val = row.get('体_运动量', '')
    max_val = row.get('随_最大运动量')
    min_val = row.get('随_最小运动量')
    
    f_int = 0.0
    issue_list = []
    
    if pd.isna(max_val) or pd.isna(min_val):
        return f_int, ''
    
    # 条件1：随访不运动 vs 体检显示常运动
    if max_val == 0 and min_val == 0 and exam_val in ['每天', '每周一次以上']:
        f_int -= 0.5
        issue_list.append('运动差异')
    
    # 条件2：随访有运动 vs 体检不锻炼
    if max_val > 0 and min_val > 0 and exam_val == '不锻炼':
        f_int -= 0.5
        issue_list.append('运动差异')
    
    # 条件3：随访运动量差异过大
    if abs(max_val - min_val) >= 6:
        f_int -= 0.5
        issue_list.append('运动差>=6')
    
    return f_int, ';'.join(issue_list)

def main():
    print("=" * 60)
    print("【糖尿病随访与体检数据核查（积分制）】-2025.08.09...")
    print("功能说明:")
    print("  1. 整合随访与体检数据")
    print("  2. 采用积分制核查差异（初始0分，差异扣分）")
    print("  3. 差异分<-1视为不符合要求")
    print("-" * 50)
    
    # 1. 读取随访数据并进行聚合
    print("步骤1: 读取糖尿病随访数据...")
    try:
        follow_up = pd.read_excel('糖尿病随访明细.xlsx')
    except FileNotFoundError:
        print("❌❌ 错误：找不到 '糖尿病随访明细.xlsx' 文件")
        return
    except Exception as e:
        print(f"❌❌ 读取随访数据时出错: {str(e)}")
        return
    
    # 检查必要列是否存在
    required_follow_cols = [
        '身份证号', '所属管辖', '姓名', 
        '身高', '体重', '日吸烟量', 
        '饮酒量', '周运动量'
    ]
    
    missing_cols = [col for col in required_follow_cols if col not in follow_up.columns]
    if missing_cols:
        print(f"❌❌ 随访数据中缺失必要列: {', '.join(missing_cols)}")
        return
    
    # 保留所需列并聚合
    print("步骤2: 按身份证号进行分别组合...")
    follow_agg = follow_up.groupby('身份证号').agg(
        所属管辖=('所属管辖', 'first'),
        姓名=('姓名', 'first'),
        随_最大身高=('身高', 'max'),
        随_最小身高=('身高', 'min'),
        随_最大体重=('体重', 'max'),
        随_最小体重=('体重', 'min'),
        随_最大吸烟量=('日吸烟量', 'max'),
        随_最小吸烟量=('日吸烟量', 'min'),
        随_最大饮酒量=('饮酒量', 'max'),
        随_最小饮酒量=('饮酒量', 'min'),
        随_最大运动量=('周运动量', 'max'),
        随_最小运动量=('周运动量', 'min')
    ).reset_index()
    
    # 2. 读取体检数据
    print("步骤3: 读取体检数据...")
    exam_dir = '临时_核查结果'
    exam_file = os.path.join(exam_dir, '体检明细表_已核查.xlsx')
    
    try:
        exam_df = pd.read_excel(exam_file)
    except FileNotFoundError:
        print(f"❌❌ 错误：找不到 '{exam_file}' 文件")
        return
    except Exception as e:
        print(f"❌❌ 读取体检数据时出错: {str(e)}")
        return
    
    # 检查必要列是否存在
    required_exam_cols = [
        '身份证号',
        '体重', '身高', 
        '吸烟情况|状况', '饮酒情况|频率', 
        '饮酒情况|是否戒酒', '体育锻炼|频率'
    ]
    
    missing_cols = [col for col in required_exam_cols if col not in exam_df.columns]
    if missing_cols:
        print(f"❌❌ 体检数据中缺失必要列: {', '.join(missing_cols)}")
        return
    
    # 重命名体检列
    exam_df = exam_df.rename(columns={
        '体重': '体_体重',
        '身高': '体_身高',
        '吸烟情况|状况': '体_吸烟量',
        '饮酒情况|频率': '体_饮酒量',
        '饮酒情况|是否戒酒': '体_是否戒酒',
        '体育锻炼|频率': '体_运动量'
    })
    
    # 只保留所需列
    exam_keep_cols = ['身份证号', '体_体重', '体_身高', 
                      '体_吸烟量', '体_饮酒量', '体_是否戒酒', '体_运动量']
    exam_df = exam_df[exam_keep_cols]
    
    # 3. 合并随访聚合数据与体检数据
    print("步骤4: 合并随访与体检数据...")
    merged_df = pd.merge(
        follow_agg,
        exam_df,
        on='身份证号',
        how='left'
    )
    
    # 添加是否有体检表列
    merged_df['是否有体检表'] = merged_df['体_身高'].apply(lambda x: '是' if not pd.isna(x) else '否')
    
    print(f"  体检表存在率: {merged_df['是否有体检表'].eq('是').mean()*100:.2f}%...")
    
    # 4. 执行核查逻辑
    print("步骤5: 执行积分制数据核查...")
    
    # 初始化结果列
    result_columns = [
        '差异分值', '是否符合要求', '不符合要求的说明'
    ]
    
    for col in result_columns:
        merged_df[col] = None
    
    # 使用tqdm显示进度
    tqdm.pandas(desc="  进度")
    
    # 应用核查函数计算积分
    height_score, height_issue = zip(*merged_df.progress_apply(check_height, axis=1))
    weight_score, weight_issue = zip(*merged_df.progress_apply(check_weight, axis=1))
    smoke_score, smoke_issue = zip(*merged_df.progress_apply(check_smoke, axis=1))
    alcohol_score, alcohol_issue = zip(*merged_df.progress_apply(check_alcohol, axis=1))
    exercise_score, exercise_issue = zip(*merged_df.progress_apply(check_exercise, axis=1))
    
    # 计算总积分
    merged_df['差异分值'] = (
        np.array(height_score) + 
        np.array(weight_score) + 
        np.array(smoke_score) + 
        np.array(alcohol_score) + 
        np.array(exercise_score)
    )
    
    # 合并所有不符合要求的说明
    merged_df['不符合要求的说明'] = [
        ';'.join(filter(None, [h, w, s, a, e]))
        for h, w, s, a, e in zip(height_issue, weight_issue, smoke_issue, alcohol_issue, exercise_issue)
    ]
    
    # 计算是否符合要求
    merged_df['是否符合要求'] = merged_df['差异分值'].apply(
        lambda x: '是' if x > -1 else '否'
    )
    
    # 5. 保存结果
    output_file = os.path.join(exam_dir, '糖尿病_全年随访体检.xlsx')
    output_cols = [
        '所属管辖', '姓名', '身份证号',
        '随_最大身高', '随_最小身高',
        '随_最大体重', '随_最小体重',
        '随_最大吸烟量', '随_最小吸烟量',
        '随_最大饮酒量', '随_最小饮酒量',
        '随_最大运动量', '随_最小运动量',
        '是否有体检表', '体_体重', '体_身高', 
        '体_吸烟量', '体_饮酒量', '体_是否戒酒', '体_运动量',
        '差异分值', '是否符合要求', '不符合要求的说明'
    ]
    
    # 确保所有列存在
    final_df = merged_df.reindex(columns=output_cols)
    
    os.makedirs(exam_dir, exist_ok=True)
    try:
        final_df.to_excel(output_file, index=False)
        print(f"✅ 核查完成！结果已保存至: {output_file}")
        
        # 打印统计摘要
        print("-" *50)
        print(f"总记录数: {len(final_df)}")
        print(f"符合要求人数: {final_df['是否符合要求'].eq('是').sum()}")
        print(f"不符合要求人数: {final_df['是否符合要求'].eq('否').sum()}")
        print(f"平均差异分值: {final_df['差异分值'].mean():.2f}")
        print(f"最大扣分: {final_df['差异分值'].min():.2f}")
        print(f"体检率: {final_df['是否有体检表'].eq('是').mean()*100:.2f}%")
        
        # 扣分分布统计
        print("\n扣分分布:")
        score_bins = [-10, -3, -2, -1, 0]
        score_labels = ['<=-3分', '-3~-2分', '-2~-1分', '>-1分']
        score_dist = pd.cut(final_df['差异分值'], bins=score_bins, labels=score_labels).value_counts()
        print(score_dist)
        
    except Exception as e:
        print(f"❌❌ 保存结果时出错: {str(e)}")

if __name__ == "__main__":
    main()

####################################################################################################
##此段代码为同天两病随访数据比较
####################################################################################################
import pandas as pd
import numpy as np
import os
from tqdm import tqdm

# 设置显示选项（调试时使用）
pd.set_option('display.max_columns', None)

# 输入文件路径
high_bp_file = '高血压随访明细.xlsx'
diabetes_file = '糖尿病随访明细.xlsx'
output_folder = '临时_核查结果'
output_file = os.path.join(output_folder, '糖尿病_两病同天随访.xlsx')

# 确保输出文件夹存在
os.makedirs(output_folder, exist_ok=True)

# 步骤1: 加载数据
print("=" * 60)
print("【两病同日随访数据一致性核对】-2025.07.30...")
print("功能说明:")
print("  1. 查找同日随访")
print("  2. 判断常规项是否一致")
print("-" * 50)

print("步骤1: 加载随访数据文件...")
df_high = pd.read_excel(high_bp_file)
df_diab = pd.read_excel(diabetes_file)

# 步骤2: 数据预处理
print("步骤2: 预处理...")

# 重命名两个数据集的列（避免合并冲突）
df_high = df_high.rename(columns={
    '收缩压': '高_收缩压', '舒张压': '高_舒张压',
    '身高': '高_身高', '体重': '高_体重',
    '每日吸烟量': '高_吸烟量', '每日饮酒量': '高_饮酒量',
    '每周运动次数': '高_运动量', '每次运动时间': '高_运动时长'
})

df_diab = df_diab.rename(columns={
    '收缩压': '糖_收缩压', '舒张压': '糖_舒张压',
    '身高': '糖_身高', '体重': '糖_体重',
    '日吸烟量': '糖_吸烟量', '饮酒量': '糖_饮酒量',
    '周运动量': '糖_运动量', '每次运动时间': '糖_运动时长'
})

# 基础列（用于合并和结果）
base_columns = ['所属管辖', '姓名', '身份证号', '随访日期']

# 合并两个数据集（内连接，仅保留同天随访记录）
print("步骤3: 合并并核查一致性...")
merged = pd.merge(
    df_high[base_columns + ['高_' + col for col in ['收缩压', '舒张压', '身高', '体重', '吸烟量', '饮酒量', '运动量', '运动时长']]],
    df_diab[['身份证号', '随访日期'] + ['糖_' + col for col in ['收缩压', '舒张压', '身高', '体重', '吸烟量', '饮酒量', '运动量', '运动时长']]],
    on=['身份证号', '随访日期'],
    how='inner'
)

# 进度条初始化
tqdm.pandas(desc="  进度")

# 核查函数
def check_consistency(row):
    """检查一行数据中各字段的一致性"""
    inconsistencies = []
    
    # 1. 检查血压
    if (row['高_收缩压'] != row['糖_收缩压']) or (row['高_舒张压'] != row['糖_舒张压']):
        inconsistencies.append("血压不一致")
    
    # 2. 检查其他指标
    if row['高_身高'] != row['糖_身高']:
        inconsistencies.append("身高不一致")
    if row['高_体重'] != row['糖_体重']:
        inconsistencies.append("体重不一致")
    if row['高_吸烟量'] != row['糖_吸烟量']:
        inconsistencies.append("吸烟量不一致")
    if row['高_饮酒量'] != row['糖_饮酒量']:
        inconsistencies.append("饮酒量不一致")
    if row['高_运动量'] != row['糖_运动量']:
        inconsistencies.append("运动量不一致")
    if row['高_运动时长'] != row['糖_运动时长']:
        inconsistencies.append("运动时长不一致")
    
    # 3. 设置一致性标志
    is_consistent = '是' if not inconsistencies else '否'
    
    return pd.Series([is_consistent, ", ".join(inconsistencies)])

# 应用核查函数（使用tqdm显示进度）
result = merged.progress_apply(check_consistency, axis=1)
merged[['是否一致', '不一致说明']] = result

# 重新排列列顺序（按规范要求）
output_columns = base_columns + [
    '高_收缩压', '高_舒张压', '糖_收缩压', '糖_舒张压',
    '高_身高', '糖_身高', '高_体重', '糖_体重',
    '高_吸烟量', '糖_吸烟量', '高_饮酒量', '糖_饮酒量',
    '高_运动量', '糖_运动量', '高_运动时长', '糖_运动时长',
    '是否一致', '不一致说明'
]
final_df = merged[output_columns]

# 步骤4: 保存结果
print("步骤4: 保存核查文件...")
final_df.to_excel(output_file, index=False)
print("-" * 50)
print(f"共处理 {len(merged)} 条同天随访记录")

####################################################################################################
##此段代码为统计每季度随访频次
####################################################################################################
import pandas as pd
import os
from datetime import datetime
from tqdm import tqdm
import time

def main():
    start_time = datetime.now()
    print("=" * 60)
    print("【糖尿病随访频次统计】-2025.08.09...")
    print("功能说明:")
    print("  1. 提取所需数据")
    print("  2. 统计每患者每季度的随访次数")
    print("  3. 按季度判断随访频次是否达标")
    print("-" * 50)
    
    # =========================================================================
    # 1. 配置参数和路径
    INPUT_FILE = "糖尿病随访明细.xlsx"
    OUTPUT_DIR = "临时_核查结果"
    OUTPUT_FILE = os.path.join(OUTPUT_DIR, "糖尿病_随访频次.xlsx")
    
    print(f"步骤1: 配置信息...")
    
    # 创建输出目录（如果不存在）
    if not os.path.exists(OUTPUT_DIR):
        os.makedirs(OUTPUT_DIR)
        print(f"  已创建输出目录: {OUTPUT_DIR}...")

    # =========================================================================
    # 2. 数据读取与预处理
    print(f"步骤2: 正在读取随访数据文件...")
    try:
        raw_df = pd.read_excel(
            INPUT_FILE,
            usecols=["所属管辖", "姓名", "身份证号", "随访日期"],
            dtype={"身份证号": str}  # 确保身份证号作为字符串处理
        )
    except Exception as e:
        print(f"  错误: 文件读取失败 - {str(e)}")
        return
    
    # 检查必要的列是否存在
    REQUIRED_COLS = ["所属管辖", "姓名", "身份证号", "随访日期"]
    missing_cols = [col for col in REQUIRED_COLS if col not in raw_df.columns]
    if missing_cols:
        print(f"  错误: 文件缺少必要的列 - {', '.join(missing_cols)}")
        return

    # 数据清洗
    original_count = len(raw_df)
    clean_df = raw_df.dropna(subset=["身份证号", "随访日期"]).copy()
    clean_df["随访日期"] = pd.to_datetime(clean_df["随访日期"], errors="coerce")
    clean_df = clean_df.dropna(subset=["随访日期"])
    
    # 统计数据清洗结果
    invalid_count = original_count - len(clean_df)
    if invalid_count > 0:
        print(f"  警告: 过滤 {invalid_count} 条无效记录 (缺失身份证号/随访日期/无效日期)")
    
    # 提取年份和季度信息
    print(f"步骤3: 提取季度信息...")
    clean_df["年份"] = clean_df["随访日期"].dt.year
    clean_df["季度"] = clean_df["随访日期"].dt.quarter
    
    # 仅保留2025年的随访记录
    df_2025 = clean_df[clean_df["年份"] == 2025].copy()
    other_year_count = len(clean_df) - len(df_2025)
    if other_year_count > 0:
        print(f"  提示: 过滤 {other_year_count} 条非2025年的随访记录")

    # =========================================================================
    # 3. 随访频次统计与核查
    print(f"步骤4: 正在统计随访频次...")
    
    # 获取唯一患者列表
    unique_patients = df_2025[["所属管辖", "身份证号", "姓名"]].drop_duplicates()
    patient_count = len(unique_patients)
    
    # 按患者分组
    grouped = df_2025.groupby(["所属管辖", "身份证号", "姓名"])
    
    # 准备结果数据容器
    results = []
    
    # 使用进度条可视化处理过程
    with tqdm(total=patient_count, desc="  进度", unit="人", 
              bar_format="{l_bar}{bar}| {n_fmt}/{total_fmt} [预计剩余:{remaining}]") as pbar:
        for (gov, id_card, name), group in grouped:
            # 统计各季度随访次数
            q1_records = group[group["季度"] == 1]
            q2_records = group[group["季度"] == 2]
            q3_records = group[group["季度"] == 3]
            q4_records = group[group["季度"] == 4]
            
            q1_count = len(q1_records)
            q2_count = len(q2_records)
            q3_count = len(q3_records)
            q4_count = len(q4_records)
            
            # 判断各季度是否有随访
            q1_freq = "是" if q1_count > 0 else "否"
            q2_freq = "是" if q2_count > 0 else "否"
            q3_freq = "是" if q3_count > 0 else "否"
            q4_freq = "是" if q4_count > 0 else "否"
            
            # 添加到结果集
            results.append([
                gov, name, id_card, 
                q1_count, q2_count, q3_count, q4_count,
                q1_freq, q2_freq, q3_freq, q4_freq
            ])
            
            # 更新进度条
            pbar.update(1)
    
    # =========================================================================
    # 4. 结果整理与输出  定义结果列名
    COLUMN_NAMES = [
        "所属管辖", "姓名", "身份证号",
        "一季度随访次数", "二季度随访次数", "三季度随访次数", "四季度随访次数",
        "一季度频次", "二季度频次", "三季度频次", "四季度频次"
    ]
    
    # 创建DataFrame
    result_df = pd.DataFrame(results, columns=COLUMN_NAMES)
    
    # 按身份证号排序
    result_df = result_df.sort_values("身份证号")
    
    # 保存结果到Excel
    print(f"步骤5: 正在导出结果...")
    try:
        result_df.to_excel(OUTPUT_FILE, index=False)
    except Exception as e:
        print(f"  错误: 导出失败 - {str(e)}")
        return
    
    # =========================================================================
    # 5. 性能统计与结束
    end_time = datetime.now()
    elapsed = (end_time - start_time).total_seconds()
    
    print("-" * 50)
    print(f"  有效随访记录: {len(df_2025):,}条")
    print(f"  处理患者数量: {patient_count:,}人")
    print(f"  总耗时: {elapsed:.2f}秒")

if __name__ == "__main__":
    main()


####################################################################################################
##此段代码为随访逻辑核查、有无居民档案
####################################################################################################
import pandas as pd
import re
import numpy as np
from datetime import datetime
from tqdm import tqdm
import os
import warnings
warnings.filterwarnings('ignore')

def get_spring_festivals(years):
    """计算2025-2030年春节日期（正月初一和初二）"""
    # 实际春节日期 (正月初一和初二)
    spring_dates = {
        2025: ['2025-01-29', '2025-01-30'],
        2026: ['2026-02-17', '2026-02-18'],
        2027: ['2027-02-06', '2027-02-07'],
        2028: ['2028-01-26', '2028-01-27'],
        2029: ['2029-02-13', '2029-02-14'],
        2030: ['2030-02-03', '2030-02-04']
    }
    all_dates = []
    for year in years:
        all_dates.extend(spring_dates.get(year, []))
    return all_dates

# 步骤1: 加载数据
print("=" * 60)
print("【糖尿病随访提醒部分核查】-2025.08.09...")
print("功能说明:")
print("  1. 核查特殊日期的随访、体检")
print("  2. 核查同患者同天录入多次随访")
print("  3. 核查录入日期在随访日期之前")
print("  4. 核查对上次转诊以后的备注")
print("-" * 50)
print("步骤1: 正在加载数据...")
data_path = '糖尿病随访明细.xlsx'
if not os.path.exists(data_path):
    raise FileNotFoundError("找不到糖尿病随访明细.xlsx文件")

df = pd.read_excel(data_path)

# 确保必要的列存在
required_columns = ['所属管辖', '姓名', '身份证号', '随访日期', '录入时间', '转诊原因', '备注']
for col in required_columns:
    if col not in df.columns:
        raise ValueError(f"数据中缺少必要的列: {col}")

# 添加日期字段
df['随访日期'] = pd.to_datetime(df['随访日期'], errors='coerce')
df['录入时间'] = pd.to_datetime(df['录入时间'], errors='coerce')
df['录入日期'] = df['录入时间'].dt.date  # 用于同天录入核查

# 步骤2: 准备核查结果
print("步骤2: 开始数据处理...")
output_cols = ['所属管辖', '姓名', '身份证号', '特殊日期的随访体检', 
              '对上次转诊的备注', '同天录入多次随访（3及以上）', 
              '录入日期逻辑问题', '用药一致性', '其他']

# 获取春节日期
spring_festivals = get_spring_festivals(range(2025, 2031))

# 按身份证号分组处理
grouped = df.groupby('身份证号', as_index=False)
results = []
id_groups = list(grouped)

# 进度条设置
pbar = tqdm(total=len(id_groups), desc="  进度")

for _, group in id_groups:
    # 获取基本信息
    base_info = {
        '所属管辖': group['所属管辖'].iloc[0],
        '姓名': group['姓名'].iloc[0],
        '身份证号': group['身份证号'].iloc[0],
        '特殊日期的随访体检': '',
        '对上次转诊的备注': '',
        '同天录入多次随访（3及以上）': '',
        '录入日期逻辑问题': '',
        '用药一致性': '',
        '其他': ''
    }
    
    # 按照随访日期排序
    group = group.sort_values('随访日期')
    group = group.reset_index(drop=True)
    
    # 核查1: 特殊日期的随访体检
    festival_issues = []
    for _, row in group.iterrows():
        if pd.isnull(row['随访日期']):
            continue
            
        # 转换为字符串格式比较
        follow_date_str = row['随访日期'].strftime('%Y-%m-%d')
        if follow_date_str in spring_festivals:
            festival_issues.append(f"{follow_date_str}随访真实性不高")
                
    base_info['特殊日期的随访体检'] = ';'.join(festival_issues)
    
    # 核查2: 对上次转诊的备注
    transfer_issues = []
    for i in range(len(group) - 1):
        current_row = group.iloc[i]
        next_row = group.iloc[i + 1]
        
        # 检查转诊原因（提取汉字）
        if not pd.isnull(current_row['转诊原因']):
            chinese_text = re.sub(r'[^\u4e00-\u9fa5]', '', str(current_row['转诊原因']))
            if chinese_text.strip() != '':
                # 检查下次随访备注
                if pd.isnull(next_row['备注']) or '转诊' not in str(next_row['备注']):
                    follow_date = next_row['随访日期'].strftime('%Y-%m-%d')
                    transfer_issues.append(f"{follow_date}未备注转诊情况")
    
    base_info['对上次转诊的备注'] = ';'.join(transfer_issues)
    
    # 核查3: 同天录入多次随访
    if group['录入日期'].value_counts().max() >= 3:
        base_info['同天录入多次随访（3及以上）'] = '同患者同日录入3次及以上随访'
    
    # 核查4: 录入日期逻辑问题
    logic_issues = []
    for _, row in group.iterrows():
        if not pd.isnull(row['随访日期']) and not pd.isnull(row['录入时间']):
            if row['录入时间'] < row['随访日期']:
                follow_date = row['随访日期'].strftime('%Y-%m-%d')
                logic_issues.append(f"{follow_date}录入日期在随访日期之前")
                
    base_info['录入日期逻辑问题'] = ';'.join(logic_issues)
    
    results.append(base_info)
    pbar.update(1)

pbar.close()

# 步骤3: 保存结果
print("步骤3: 正在保存核查结果...")
result_df = pd.DataFrame(results, columns=output_cols)

# 创建输出目录
output_dir = '临时_核查结果'
os.makedirs(output_dir, exist_ok=True)
output_path = os.path.join(output_dir, '糖尿病_提醒.xlsx')

result_df.to_excel(output_path, index=False)
print("-" * 50)
print(f"共处理 {len(results)} 个患者的核查数据")
print("-" * 50)
#---------------------------------------------------------------------------------------------------
import pandas as pd
import os
from tqdm import tqdm

def check_missing_records():
    # 定义文件路径
    resident_file = '居民档案.xlsx'
    reminder_file = './临时_核查结果/糖尿病_提醒.xlsx'
    
    # 检查文件是否存在
    if not os.path.exists(resident_file):
        print(f"错误：找不到居民档案文件 '{resident_file}'")
        return
    
    if not os.path.exists(reminder_file):
        print(f"错误：找不到糖尿病提醒文件 '{reminder_file}'")
        return
    
    try:
        # 读取居民档案（只读取需要的列以提高速度）
        print("步骤1: 正在加载居民档案...")
        resident_df = pd.read_excel(resident_file, usecols=['身份证号'])
        resident_ids = set(resident_df['身份证号'].astype(str).str.strip())
        print(f"已加载 {len(resident_ids)} 条居民档案记录...")
        
        # 读取糖尿病提醒文件
        reminder_df = pd.read_excel(reminder_file)
        
        if '其他' not in reminder_df.columns:
            reminder_df['其他'] = ''
        
        # 处理数据并显示进度条
        total_rows = len(reminder_df)
        missing_count = 0
        
        print("步骤2: 正在核查是否存在居民档案...")
        
        # 使用tqdm显示进度条
        for i in tqdm(range(total_rows), desc="  进度"):
            id_card = str(reminder_df.at[i, '身份证号']).strip()
            
            # 检查身份证是否在居民档案中
            if id_card not in resident_ids:
                reminder_df['其他'] = reminder_df['其他'].astype(str)
                reminder_df.at[i, '其他'] = '未发现居民档案'
                missing_count += 1
        
        # 保存结果（文件名不变）
        print(f"发现 {missing_count} 条无对应档案的随访记录，正在保存结果...")
        
        # 创建一个临时文件以确保原子性写入
        temp_output = './临时_核查结果/糖尿病_提醒_temp.xlsx'
        
        with pd.ExcelWriter(temp_output, engine='openpyxl') as writer:
            reminder_df.to_excel(writer, index=False)
        
        # 替换原文件（Windows下需要先删除原文件）
        if os.path.exists(reminder_file):
            os.remove(reminder_file)
        
        os.rename(temp_output, reminder_file)
        
        print("下一步...")
    
    except Exception as e:
        print(f"处理过程中发生错误: {str(e)}")

if __name__ == "__main__":
    check_missing_records()
#------------------------------------------------------------------------------
#足背动脉提醒
import pandas as pd
import os
from datetime import datetime

# 文件路径设置
current_dir = os.getcwd()
detail_file = os.path.join(current_dir, '糖尿病随访明细.xlsx')
reminder_dir = os.path.join(current_dir, '临时_核查结果')
reminder_file = os.path.join(reminder_dir, '糖尿病_提醒.xlsx')

# 创建目标文件夹（如果不存在）
os.makedirs(reminder_dir, exist_ok=True)

# 读取糖尿病随访数据
df_detail = pd.read_excel(detail_file)

# 确保"随访日期"是日期类型
if not pd.api.types.is_datetime64_any_dtype(df_detail['随访日期']):
    df_detail['随访日期'] = pd.to_datetime(df_detail['随访日期'], errors='coerce')
    
    # 尝试另一种日期格式转换方式
    if df_detail['随访日期'].isna().any():
        # 如果转换失败，尝试不同的格式转换
        df_detail['随访日期'] = df_detail['随访日期'].fillna(method='ffill').astype(str).apply(
            lambda x: datetime.strptime(x, "%Y-%m-%d") if len(x) > 5 else pd.NaT
        )

# 处理逻辑：筛选需要提醒的记录
# 创建安全条件，防止空值干扰
has_valid_dorsalis = '足背动脉搏动' in df_detail.columns
if has_valid_dorsalis:
    # 处理可能的空值并确保数据类型一致
    df_detail['足背动脉搏动'] = df_detail['足背动脉搏动'].fillna('').astype(str)
    
    # 筛选条件：不是'触及正常'且不为空字符串
    mask = (df_detail['足背动脉搏动'] != '触及正常') & (df_detail['足背动脉搏动'].str.strip() != '')
else:
    print("警告：数据文件中缺少'足背动脉搏动'列")
    mask = pd.Series(False, index=df_detail.index)

# 获取需要提醒的数据
df_alert = df_detail.loc[mask, ['身份证号', '随访日期']].copy()

# 处理日期格式并构建提醒内容
# 使用安全的日期格式化方法
def safe_format_date(date_val):
    try:
        if pd.notna(date_val):
            if isinstance(date_val, str):
                # 如果还是字符串类型，尝试解析
                date_val = pd.to_datetime(date_val, errors='coerce')
            if pd.notna(date_val) and hasattr(date_val, 'strftime'):
                return date_val.strftime('%Y-%m-%d')
        return ''  # 对于无效日期返回空字符串
    except Exception:
        return ''

# 构建提醒内容
#df_alert['提醒内容'] = df_alert['随访日期'].apply(safe_format_date) + '足背动脉待复核'
df_alert['提醒内容'] = df_alert['随访日期'].dt.strftime('%Y-%m-%d') + '足背动脉待复核'

# 处理目标提醒文件
if os.path.exists(reminder_file):
    df_reminder = pd.read_excel(reminder_file)
    
    # 创建或更新'足背动脉'列
    if '足背动脉' not in df_reminder.columns:
        df_reminder['足背动脉'] = ''
    
    # 创建更新字典
    update_dict = dict(zip(df_alert['身份证号'], df_alert['提醒内容']))
    
    # 更新足背动脉列
    def update_dorsalis(row):
        if row['身份证号'] in update_dict:
            return update_dict[row['身份证号']]
        return row.get('足背动脉', '')
    
    df_reminder['足背动脉'] = df_reminder.apply(update_dorsalis, axis=1)
else:
    # 新建提醒文件
    df_reminder = pd.DataFrame({
        '身份证号': df_alert['身份证号'],
        '足背动脉': df_alert['提醒内容']
    })

# 保存结果
df_reminder.to_excel(reminder_file, index=False)

print(f"成功处理 {len(df_alert)} 条提醒记录")
print(f"提醒文件已保存至: {reminder_file}")

####################################################################################################
##此段代码为核查用药一致性3-20250811
####################################################################################################
import pandas as pd
import numpy as np
import os
import warnings

warnings.filterwarnings('ignore', category=UserWarning, module='openpyxl')

STANDARD_MEDICINES = [
    '双胍',
    '格列本脲', '格列吡嗪', '格列喹酮', '格列美脲', '格列齐特','格列苯脲',
    '吡格列酮', '罗格列酮',
    '阿卡波糖', '伏格列波糖', '米格列醇',
    '瑞格列奈', '那格列奈', '米格列奈',
    '阿格列汀', '利格列汀', '沙格列汀', '维格列汀', '西格列汀', '替格列汀', '福格列汀', '考格列汀', '瑞格列汀',
    '艾托格列净', '达格列净', '恩格列净', '卡格列净', '恒格列净', '加格列净',
    '多格列艾汀',
    '德谷胰岛素', '地特胰岛素', '甘精胰岛素', '长秀霖', '来优时', '诺和平', '惠优达',
    '低精蛋白锌胰岛素', '甘舒霖', '万苏林',
    '门冬胰岛素', '赖脯胰岛素', '谷赖胰岛素', '人胰岛素', '艾倍得', '诺和锐', '优泌乐', '优思灵', '重和林',
    '诺和灵', '优泌林', '万邦林',
    '诺和达', '优泌安',
    '地诺前列酮', '芦比前列酮',
    '消渴','参芪'
]

MED_SUFFIXES = ['胶囊', '片', '粒', '丸', '缓释', '控释', '口服', '注射', '液体', '剂', ' ', '(', ')', '（', '）']

def preprocess_medication(text):
    if pd.isna(text) or text is None or text == '':
        return ''
    
    text = str(text).strip() 
    for suffix in MED_SUFFIXES:
        text = text.replace(suffix, '')
    return text

def extract_standard_meds(text):# 提取规范药名（只保留在STANDARD_MEDICINES中的药名）
    preprocessed = preprocess_medication(text)
    if not preprocessed:
        return set()
    return {med for med in STANDARD_MEDICINES if med in preprocessed}

def check_consistency(follow_before_meds, follow_after_meds, exam_meds):
    """检查用药一致性"""

    # 如果随访用药前存在且被体检表包含
    if follow_before_meds and follow_before_meds.issubset(exam_meds):
        return '一致'
    
    # 如果随访用药后存在且被体检表包含
    if follow_after_meds and follow_after_meds.issubset(exam_meds):
        return '一致'
    
    # 如果两个随访用药都不存在
    if not follow_before_meds and not follow_after_meds:
        return '一致'
    
    #如随访前无用药，体检无用药，一致（8.11）
    if not follow_before_meds and not exam_meds:
        return '一致'

    #如随访后无用药，体检无用药，一致（8.11）
    if not follow_after_meds and not exam_meds:
        return '一致'

    # 其他情况均为不一致
    return '不一致'

def main():
    base_dir = os.getcwd()
    temp_dir = os.path.join(base_dir, '临时_核查结果')
    followup_file = os.path.join(base_dir, '糖尿病随访明细.xlsx')
    exam_merged_file = os.path.join(temp_dir, '体检明细表_已核查.xlsx')
    reminder_file = os.path.join(temp_dir, '糖尿病_提醒.xlsx')
    output_file = os.path.join(temp_dir, '糖尿病_用药一致性.xlsx')
    
    print(f"步骤1: 开始读取数据...")
    
    try:
        exam_df = pd.read_excel(exam_merged_file, engine='openpyxl')
        followup_df = pd.read_excel(followup_file, engine='openpyxl')
        reminder_df = pd.read_excel(reminder_file, engine='openpyxl')
    except Exception as e:
        print(f"文件读取失败: {e}")
        return
    
    if '体检日期' in exam_df.columns:
        exam_df['体检日期'] = pd.to_datetime(exam_df['体检日期'], errors='coerce')
    if '随访日期' in followup_df.columns:
        followup_df['随访日期'] = pd.to_datetime(followup_df['随访日期'], errors='coerce')
    
    print(f"步骤2: 构建糖尿病患者名单...")
    hyper_yes_ids = set()
    hyper_yes_ids = set(followup_df['身份证号'].dropna().astype(str).unique())
    
    print(f"步骤3: 提取所属管辖和姓名等基础信息...")
    id_to_info = {}
    for _, row in followup_df.iterrows():
        id_card = row.get('身份证号')
        if not id_card:
            continue
        id_card = str(id_card).strip()
        
        if id_card not in id_to_info:
            id_to_info[id_card] = (
                row.get('所属管辖', ''),
                row.get('姓名', '')
            )
    
    # 6. 处理随访数据 - 分组以便快速访问
    followup_dict = {}
    for _, row in followup_df.iterrows():
        id_card = row.get('身份证号')
        if not id_card:
            continue
        id_card = str(id_card).strip()
        
        if id_card not in followup_dict:
            followup_dict[id_card] = []
        
        #此处胰岛素名称
        record = { '随访日期': row.get('随访日期'), '胰岛素种类': row.get('胰岛素种类', '') ,'用药情况': row.get('用药情况', '') }
        followup_dict[id_card].append(record)
    
    # 7. 准备结果数据结构
    results = []
    total_rows = len(exam_df)
    print(f"步骤4: 开始处理体检记录，共 {total_rows} 条...")
    
    # 8. 处理每条体检记录
    bar_length = 50
    for idx, row in enumerate(exam_df.itertuples(), 1):
        progress = idx / total_rows
        if idx % max(1, total_rows // 100) == 0 or idx == total_rows:
            block = int(bar_length * progress)
            progress_bar = f"[{'=' * block}{'>' if block < bar_length else '='}{' ' * (bar_length - block)}]"
            print(f"\r  进度: {progress_bar} {idx}/{total_rows} ({progress:.1%})", end="", flush=True)
        
        id_card = getattr(row, '身份证号', None)
        if not id_card:
            continue
        id_card = str(id_card).strip()
        
        exam_date = getattr(row, '体检日期', None)
        
        is_hyper = '是' if id_card in hyper_yes_ids else '否'  # 判断是否糖尿病患者
        
        jurisdiction, name = id_to_info.get(id_card, ('', ''))# 从随访表获取管辖和姓名
        
        # 初始化结果行 - 修改列名并新增两列
        result_row = {
            '所属管辖': jurisdiction,
            '姓名': name,
            '身份证号': id_card,
            '体检日期': exam_date.strftime('%Y-%m-%d') if pd.notnull(exam_date) else '',
            '是否糖尿病': is_hyper,
            '随访日期前': '',  # 修改列名
            '随访用药前': '',  # 修改列名
            '随访日期后': '',  # 新增列
            '随访用药后': '',  # 新增列
            '体检表用药': getattr(row, '主要用药情况', ''),
            '用药一致性': ''
        }
        
        # 9. 检查核查条件：体检日期非空且是糖尿病患者
        if pd.notnull(exam_date) and is_hyper == '是':
            # 初始化两个记录
            follow_before = None  # 体检日期前的最近记录
            follow_after = None   # 体检日期后的最近记录
            min_days_diff_before = float('inf')
            min_days_diff_after = float('inf')
            
            for record in followup_dict.get(id_card, []):
                follow_date = record.get('随访日期')
                if not follow_date or pd.isna(follow_date):
                    continue
                
                days_diff = (exam_date - follow_date).days
                
                # 体检日期前的随访记录
                if days_diff >= 0:  # 随访在体检前
                    if days_diff < min_days_diff_before:
                        min_days_diff_before = days_diff
                        follow_before = record
                
                # 体检日期后的随访记录
                else:  # 随访在体检后
                    after_diff = -days_diff  # 转为正数
                    if after_diff < min_days_diff_after:
                        min_days_diff_after = after_diff
                        follow_after = record
            
            # 处理体检前的随访记录
            if follow_before:
                meds = follow_before.get('用药情况', '')
                follow_date = follow_before.get('随访日期')
                
                if pd.notna(meds) and str(meds).strip():
                    result_row['随访日期前'] = follow_date.strftime('%Y-%m-%d') if pd.notnull(follow_date) else ''
                    result_row['随访用药前'] = meds
            
            # 处理体检后的随访记录
            if follow_after:
                meds = follow_after.get('用药情况', '')
                follow_date = follow_after.get('随访日期')
                
                if pd.notna(meds) and str(meds).strip():
                    result_row['随访日期后'] = follow_date.strftime('%Y-%m-%d') if pd.notnull(follow_date) else ''
                    result_row['随访用药后'] = meds
            
            # 提取规范药名
            exam_standard_meds = extract_standard_meds(result_row['体检表用药'])
            follow_before_meds = extract_standard_meds(result_row['随访用药前'])
            follow_after_meds = extract_standard_meds(result_row['随访用药后'])
            
            # 修改一致性判断逻辑
            result_row['用药一致性'] = check_consistency(follow_before_meds, follow_after_meds, exam_standard_meds)
        
        results.append(result_row)
    
    # 修改结果列顺序
    result_df = pd.DataFrame(results, columns=[
        '所属管辖', '姓名', '身份证号', '体检日期', '是否糖尿病',
        '随访日期前', '随访用药前', '随访日期后', '随访用药后',  # 修改后的列顺序
        '体检表用药', '用药一致性'
    ])
    
    print(f"\n步骤5: 正在保存用药一致性结果...")
    result_df.to_excel(output_file, index=False, engine='openpyxl')

    # 14. 更新提醒文件
    print(f"步骤6: 正在更新问题汇总文件...")
    consistency_map = result_df.set_index('身份证号')['用药一致性'].to_dict()
    
    reminder_df['用药一致性'] = reminder_df['身份证号'].map(consistency_map).fillna('')
    reminder_df.to_excel(reminder_file, index=False, engine='openpyxl')
    
    print("-" * 50)
    print(f"统计: 体检记录：{total_rows}, 有效结果：{len(result_df)}")

if __name__ == "__main__":
    print("=" * 60)
    print("【糖尿病随访体检用药核对】-2025.08.11...")
    print("功能说明:")
    print("  1. 采集体检前最近一次随访记录")
    print("  2. 采集体检后最近一次随访记录")
    print("  3. 使用常用药名字典进行一致性核对（体检表用药必须包括随访前或随访后用药）")
    print("-" * 50)
    
    try:
        main()
    except Exception as e:
        print(f"程序执行出错: {str(e)}")
        import traceback
        traceback.print_exc()

####################################################################################################
##此段代码为核查随访的增访
####################################################################################################
import pandas as pd
import numpy as np
import os
from datetime import timedelta
from tqdm import tqdm 

def process_hypertension_followup():
    # 输入输出路径配置
    input_file = "糖尿病随访明细.xlsx"
    output_file = "临时_核查结果/糖尿病_增访与加访.xlsx"
    
    # 确保输出目录存在
    os.makedirs(os.path.dirname(output_file), exist_ok=True)
    
    # 读取Excel数据
    print("=" * 60)
    print("【糖尿病随访增访核查】-2025.08.09...")
    print("功能说明:")
    print("  1. 第1次控制不满意,7-25天内必须要有1次随访")
    print("  2. 第1次连续两次控制不满意,7-25天内必须要有1次随访")
    print("  3. 第1次危急值或症状,7-25天内必须要有1次随访")
    print("-" * 50)
    print("步骤1: 重新加载糖尿病随访数据...")
    df = pd.read_excel(input_file, dtype={"身份证号": str})
    
    # 转换日期列为datetime类型
    df["随访日期"] = pd.to_datetime(df["随访日期"], errors="coerce")
    
    # 预处理：处理空值和空格
    df["症状"] = df["症状"].fillna("").astype(str).str.strip()
    df["转诊原因"] = df["转诊原因"].fillna("").astype(str).str.strip()
    
    # 排序：按身份证号和随访日期升序排序
    print("步骤2: 排序随访数据...")
    df = df.sort_values(["身份证号", "随访日期"])
    
    # 初始化结果数据结构
    result = {
        "所属管辖": [],
        "姓名": [],
        "身份证号": [],
        "首次危急值或症状增访": [],  # 规则4问题
        "年内第1次血糖控制不满意增访": [],  # 规则3问题
        "连续两次血糖控制不满意增访": [],  # 规则5问题
        "问题汇总": []
    }
    
    # 设置进度条
    unique_ids = df["身份证号"].unique()
    total_count = len(unique_ids)
    
    print(f"步骤3: 开始处理 {total_count} 名患者的随访数据...")
    
    # 按身份证号分组处理
    for id, group in tqdm(df.groupby("身份证号"), total=total_count, desc="  进度"):
        group = group.copy()
        problems = []
        rule3_problem = ""
        rule4_problem = ""
        rule5_problem = ""
        
        # 规则3：首次控制不满意的随访检查
        unsatisfied_records = group[group["随访分类"] == "控制不满意"]
        if not unsatisfied_records.empty:
            first_unsatisfied = unsatisfied_records.iloc[0]
            record_date = first_unsatisfied["随访日期"]
            
            # 格式化日期为YYYY-MM-DD
            date_str = record_date.strftime("%Y-%m-%d")
            
            # 检查后续7-25天内是否有随访
            start_date = record_date + timedelta(days=7)
            end_date = record_date + timedelta(days=25)
            subsequent_followups = group[
                (group["随访日期"] >= start_date) & 
                (group["随访日期"] <= end_date)
            ]
            
            if subsequent_followups.empty:
                rule3_problem = f"{date_str}:第1次控制不满意未在规定时段内增访"
                problems.append(rule3_problem)
        
        # 规则4：危急值/症状检查
        def is_critical(row):
            """检查是否为危急值或有效症状"""
            # 检查糖危急值
            bp_critical = row["空腹血糖"] >= 16.7 or row["空腹血糖"] <= 3.9
            
            # 检查症状有效性
            symptom_valid = row["症状"] not in ["无", "其他", ""]
            
            return bp_critical or symptom_valid
        
        critical_records = group[group.apply(is_critical, axis=1)]
        
        if not critical_records.empty:
            first_critical = critical_records.iloc[0]
            record_date = first_critical["随访日期"]
            
            # 格式化日期为YYYY-MM-DD
            date_str = record_date.strftime("%Y-%m-%d")
            
            # 检查转诊原因是否为空
            referral_empty = first_critical["转诊原因"] == ""
            
            # 检查后续7-25天内是否有随访
            start_date = record_date + timedelta(days=7)
            end_date = record_date + timedelta(days=25)
            subsequent_followups = group[
                (group["随访日期"] >= start_date) & 
                (group["随访日期"] <= end_date)
            ]
            
            if referral_empty or subsequent_followups.empty:
                if referral_empty and subsequent_followups.empty:
                    rule4_problem = f"{date_str}:第1次危急值或症状,未填转诊原因且未在规定时段内增访"
                elif referral_empty:
                    rule4_problem = f"{date_str}:第1次危急值或症状,未填转诊原因"
                else:
                    rule4_problem = f"{date_str}:第1次危急值或症状,未在规定时段内增访"
                problems.append(rule4_problem)
        
        # 规则5：连续两次控制不满意的检查
        for i in range(len(group)-1):
            current = group.iloc[i]
            next_record = group.iloc[i+1]
            
            # 检查是否连续两次控制不满意
            if current["随访分类"] == "控制不满意" and next_record["随访分类"] == "控制不满意":
                record_date = next_record["随访日期"]
                
                # 格式化日期为YYYY-MM-DD
                date_str = record_date.strftime("%Y-%m-%d")
                
                # 检查后续7-25天内是否有随访
                start_date = record_date + timedelta(days=7)
                end_date = record_date + timedelta(days=25)
                subsequent_followups = group[
                    (group["随访日期"] >= start_date) & 
                    (group["随访日期"] <= end_date)
                ]
                
                if subsequent_followups.empty:
                    rule5_problem = f"{date_str}:第1次连续2次血糖控制不满意未在规定时段内增访"
                    problems.append(rule5_problem)
                break  # 只需找到第一次连续两次情况
        
        # 添加到结果集
        first_row = group.iloc[0]
        result["所属管辖"].append(first_row["所属管辖"])
        result["姓名"].append(first_row["姓名"])
        result["身份证号"].append(id)
        result["首次危急值或症状增访"].append(rule4_problem)
        result["年内第1次血糖控制不满意增访"].append(rule3_problem)
        result["连续两次血糖控制不满意增访"].append(rule5_problem)
        result["问题汇总"].append(";".join(problems))
    
    # 创建结果DataFrame
    result_df = pd.DataFrame(result)
    
    # 保存结果
    print(f"步骤4: 保存核查: {output_file}...")
    result_df.to_excel(output_file, index=False)
    print("-" * 50)
    print(f"处理完成! 共核查 {len(result_df)} 条记录")
# 执行主程序
if __name__ == "__main__":
    process_hypertension_followup()

####################################################################################################
##此段代码为问题汇总
####################################################################################################
import pandas as pd
import os
from datetime import datetime
import warnings
warnings.filterwarnings('ignore')

# 用户输入数据时段
print("=" * 60)
print("【糖尿病规范性统计】...2025.08.09...")
print("功能说明:")
print("  1. 提取所需数据")
print("  2. 合并问题清单")
print("  3. 对规范性进行判断")
print("-" * 50)
print("提供数据时段:1(1季度)2(1-2季度)3(1-3季度)4(1-4季度)")
print("-" * 50)
period = input("请输入数据时段编号(1-4): ").strip()
while period not in ['1', '2', '3', '4']:
    print("输入无效，请重新输入!")
    period = input("请输入数据时段编号(1-4): ").strip()

# 路径设置
temp_dir = './临时_核查结果/'
output_dir = './糖尿病_核查汇总/'
os.makedirs(output_dir, exist_ok=True)

# 步骤1：读取基础数据
print("步骤1: 读取基础数据...")
base_df = pd.read_excel(os.path.join(temp_dir, '糖尿病随访表_已标记.xlsx'))
base_df = base_df[['所属管辖', '姓名', '身份证号']].drop_duplicates()
base_df.sort_values(by=['所属管辖', '姓名'], inplace=True)

# 计算年龄
current_date = datetime(2025, 8, 5) 
def calculate_age(id_card):
    if pd.isna(id_card) or not isinstance(id_card, str):
        return None
    if len(id_card) == 18:
        try:
            birth_str = id_card[6:14]
            birth_date = datetime.strptime(birth_str, '%Y%m%d')
            return (current_date - birth_date).days // 365
        except:
            return None
    return None

base_df['年龄'] = base_df['身份证号'].apply(calculate_age)

# 步骤2：处理一致性数据
print("步骤2: 合并数据不一致问题...")
result_df = base_df.copy()
result_df['一致性(既往史)'] = ''
result_df['一致性(同天随访体检)'] = ''
result_df['一致性(同天两病随访)'] = ''
result_df['一致性(体检近期随访用药)'] = ''

# 2.1 三史一致性
try:
    history_df = pd.read_excel(os.path.join(temp_dir, '糖尿病_三史一致性.xlsx'))
    mask = history_df['不一致原因'].notna()
    for idx, row in history_df[mask].iterrows():
        id_mask = result_df['身份证号'] == row['身份证号']
        result_df.loc[id_mask, '一致性(既往史)'] = row['不一致原因']
except Exception as e:
    print(f"  警告：处理三史一致性时出错 - {str(e)}")

# 2.2 同天随访体检
try:
    same_day_df = pd.read_excel(os.path.join(temp_dir, '糖尿病_同天随访体检.xlsx'))
    mask = same_day_df['不一致原因'].notna()
    for idx, row in same_day_df[mask].iterrows():
        id_mask = result_df['身份证号'] == row['身份证号']
        # 确保随访日期转换为字符串
        follow_date = pd.to_datetime(row['随访日期']).strftime('%Y-%m-%d')
        value = f"{follow_date}{row['不一致原因']}不一致"
        result_df.loc[id_mask, '一致性(同天随访体检)'] = value
except Exception as e:
    print(f"  警告：处理同天随访体检时出错 - {str(e)}")

# 2.3 两病同天随访 - 特别处理多条记录
try:
    two_disease_df = pd.read_excel(os.path.join(temp_dir, '糖尿病_两病同天随访.xlsx'))
    mask = two_disease_df['不一致说明'].notna()
    for id_card in result_df['身份证号']:
        patient_records = two_disease_df[(two_disease_df['身份证号'] == id_card) & mask]
        if not patient_records.empty:
            # 连接所有记录
            combined = []
            for _, row in patient_records.iterrows():
                # 确保随访日期转换为字符串
                follow_date = pd.to_datetime(row['随访日期']).strftime('%Y-%m-%d')
                reason = row['不一致说明'] if pd.notna(row['不一致说明']) else ''
                combined.append(f"{follow_date}{reason}")
            result_df.loc[result_df['身份证号'] == id_card, '一致性(同天两病随访)'] = ";".join(combined)
except Exception as e:
    print(f"  警告：处理两病同天随访时出错 - {str(e)}")

# 2.4 用药一致性
try:
    med_df = pd.read_excel(os.path.join(temp_dir, '糖尿病_用药一致性.xlsx'))
    mask = med_df['用药一致性'] == '不一致'
    for idx, row in med_df[mask].iterrows():
        id_mask = result_df['身份证号'] == row['身份证号']
        # 确保体检日期转换为YYYY-MM-DD格式
        exam_date = pd.to_datetime(row['体检日期']).strftime('%Y-%m-%d')
        # 处理可能为NaN的体检表用药
        meds = str(row['体检表用药']) if pd.notna(row['体检表用药']) else ''
        value = f"{exam_date}体检表:{meds}-与前后14天内随访不一致"
        result_df.loc[id_mask, '一致性(体检近期随访用药)'] = value
except Exception as e:
    print(f"  警告：处理用药一致性时出错 - {str(e)}")

# 2.5 判断整体一致性
result_df['一致性(是否一致)'] = '是'
consistency_cols = ['一致性(既往史)', '一致性(同天随访体检)', 
                   '一致性(同天两病随访)', '一致性(体检近期随访用药)']
for col in consistency_cols:
    result_df[col] = result_df[col].fillna('')
    result_df.loc[result_df[col] != '', '一致性(是否一致)'] = '否'

# 步骤3：处理体检数据
print("步骤3: 处理体检数据核查...")
result_df['体检(是否体检)'] = '否'
result_df['体检(公卫|其他)'] = ''
result_df['体检表(是否规范)'] = ''
result_df['体检表(现存主要健康问题)'] = ''
result_df['体检表(健康评价)'] = ''
result_df['体检表(健康指导)'] = ''
result_df['体检表(其他问题)'] = ''

try:
    exam_df = pd.read_excel(os.path.join(temp_dir, '体检明细表_已核查.xlsx'))
    exam_ids = exam_df['身份证号'].unique()
    result_df.loc[result_df['身份证号'].isin(exam_ids), '体检(是否体检)'] = '是'
    
    # 为处理效率，先处理体检来源
    for id_card in result_df[result_df['体检(是否体检)'] == '是']['身份证号']:
        patient_exams = exam_df[exam_df['身份证号'] == id_card]
        if not patient_exams.empty:
            # 获取最后一条体检记录（最新记录）
            latest_exam = patient_exams.iloc[-1]
            
            # 来源处理
            if '来源' in exam_df.columns:
                source = latest_exam['来源']
                if source == '公卫':
                    result_df.loc[result_df['身份证号'] == id_card, '体检(公卫|其他)'] = '公卫'
                elif source == '其他':
                    result_df.loc[result_df['身份证号'] == id_card, '体检(公卫|其他)'] = '其他'
                else:
                    result_df.loc[result_df['身份证号'] == id_card, '体检(公卫|其他)'] = ''
            
            # 填充体检表规范相关列
            if '体检表是否规范' in exam_df.columns:
                result_df.loc[result_df['身份证号'] == id_card, '体检表(是否规范)'] = latest_exam.get('体检表是否规范', '')
            if '核查健康评价' in exam_df.columns:
                result_df.loc[result_df['身份证号'] == id_card, '体检表(健康评价)'] = latest_exam.get('核查健康评价', '')
            if '核查健康指导' in exam_df.columns:
                result_df.loc[result_df['身份证号'] == id_card, '体检表(健康指导)'] = latest_exam.get('核查健康指导', '')
            if '核查其他空错项' in exam_df.columns:
                result_df.loc[result_df['身份证号'] == id_card, '体检表(其他问题)'] = latest_exam.get('核查其他空错项', '')
            
            # 处理现存主要健康问题
            if '其他系统疾病其它' in exam_df.columns:
                other_diseases = str(latest_exam['其他系统疾病其它']) if pd.notna(latest_exam['其他系统疾病其它']) else ''
                if '糖' not in other_diseases:
                    result_df.loc[result_df['身份证号'] == id_card, '体检表(现存主要健康问题)'] = '未提及糖尿病'
except Exception as e:
    print(f"  警告：处理体检数据时出错 - {str(e)}")

# 步骤4：处理随访其他问题
print("步骤4: 合并随访规范性问题...")
result_df['随访(其他)'] = ''

try:
    follow_df = pd.read_excel(os.path.join(temp_dir, '糖尿病随访表_已标记.xlsx'))
    # 只关注完整性为"否"的记录
    problematic_follows = follow_df[follow_df['完整性'] == '否']
    
    for id_card in result_df['身份证号']:
        patient_follows = problematic_follows[problematic_follows['身份证号'] == id_card]
        if not patient_follows.empty:
            combined = []
            for _, row in patient_follows.iterrows():
                # 确保随访日期转换为字符串
                follow_date = pd.to_datetime(row['随访日期']).strftime('%Y-%m-%d')
                check_result = row['核查结果'] if pd.notna(row['核查结果']) else ''
                combined.append(f"{follow_date}{check_result}")
            result_df.loc[result_df['身份证号'] == id_card, '随访(其他)'] = "；".join(combined)
except Exception as e:
    print(f"  警告：处理随访完整性时出错 - {str(e)}")

# 步骤5：处理随访频次
print("步骤5: 合并随访频次问题...")
result_df['随访(频次)'] = ''

try:
    freq_df = pd.read_excel(os.path.join(temp_dir, '糖尿病_随访频次.xlsx'))
    quarter_cols = []
    if period == '1':
        quarter_cols = ['一季度频次']
    elif period == '2':
        quarter_cols = ['一季度频次', '二季度频次']
    elif period == '3':
        quarter_cols = ['一季度频次', '二季度频次', '三季度频次']
    else:
        quarter_cols = ['一季度频次', '二季度频次', '三季度频次', '四季度频次']
    
    for id_card in result_df['身份证号']:
        patient_freq = freq_df[freq_df['身份证号'] == id_card]
        if not patient_freq.empty:
            # 只处理最新记录
            latest_freq = patient_freq.iloc[-1]
            if any(latest_freq[col] == '否' for col in quarter_cols if col in freq_df.columns):
                result_df.loc[result_df['身份证号'] == id_card, '随访(频次)'] = '不达标'
except Exception as e:
    print(f"  警告：处理随访频次时出错 - {str(e)}")

# 步骤6：处理增访与加访
print("步骤6: 合并增访与加访问题...")
result_df['随访(转诊增访)'] = ''

try:
    extra_df = pd.read_excel(os.path.join(temp_dir, '糖尿病_增访与加访.xlsx'))
    problematic_extras = extra_df[extra_df['问题汇总'].notna()]
    
    for id_card in result_df['身份证号']:
        patient_issues = problematic_extras[problematic_extras['身份证号'] == id_card]
        if not patient_issues.empty:
            # 合并所有问题
            combined_issues = ";".join(patient_issues['问题汇总'].astype(str).tolist())
            result_df.loc[result_df['身份证号'] == id_card, '随访(转诊增访)'] = combined_issues
except Exception as e:
    print(f"  警告：处理增访与加访时出错 - {str(e)}")

result_df['随访(空错项)'] = ''

# 步骤7：判断随访规范性
print("步骤7: 计算随访规范性...")
result_df['随访(是否规范)'] = '是'

# 检查各列是否有问题
follow_cols = ['随访(频次)', '随访(转诊增访)', '随访(空错项)', '随访(其他)']
for col in follow_cols:
    result_df[col] = result_df[col].fillna('')
    result_df.loc[result_df[col] != '', '随访(是否规范)'] = '否'

# 步骤9：计算总规范性
print("步骤8: 计算总体规范性...")
result_df['总规范性'] = '不规范'  # 默认设为不规范

# 填充所有可能的缺失值
result_df['一致性(是否一致)'] = result_df['一致性(是否一致)'].fillna('否')
result_df['体检表(是否规范)'] = result_df['体检表(是否规范)'].fillna('')
result_df['随访(是否规范)'] = result_df['随访(是否规范)'].fillna('否')

for idx, row in result_df.iterrows():
    if period in ['1', '2', '3']:
        # 季度：一致性一致，体检表规范或未体检，随访规范
        if (row['一致性(是否一致)'] == '是' and 
            row['随访(是否规范)'] == '是' and
            (row['体检表(是否规范)'] == '是' or row['体检表(是否规范)'] == '')):
            result_df.at[idx, '总规范性'] = '规范'
        else:
            result_df.at[idx, '总规范性'] = '不规范'
    else:
        # 年度：一致性一致，体检表规范，随访规范
        if (row['一致性(是否一致)'] == '是' and 
            row['体检表(是否规范)'] == '是' and 
            row['随访(是否规范)'] == '是'):
            result_df.at[idx, '总规范性'] = '规范'
        else:
            result_df.at[idx, '总规范性'] = '不规范'

# 步骤9：整理最终列顺序
print("步骤9: 保存核查结果...")
final_cols = [
    '所属管辖', '姓名', '身份证号', '年龄',
    '一致性(是否一致)', '一致性(既往史)', '一致性(同天随访体检)', 
    '一致性(同天两病随访)', '一致性(体检近期随访用药)',
    '体检(是否体检)', '体检(公卫|其他)', 
    '体检表(是否规范)', '体检表(现存主要健康问题)', 
    '体检表(健康评价)', '体检表(健康指导)', '体检表(其他问题)',
    '随访(是否规范)', '随访(频次)', '随访(转诊增访)', 
    '随访(空错项)', '随访(其他)',
    '总规范性'
]

# 确保所有列都存在
for col in final_cols:
    if col not in result_df.columns:
        result_df[col] = ''

# 选择最终列并排序
result_df = result_df[final_cols]

output_path = os.path.join(output_dir, '糖尿病_规范核查结果汇总.xlsx')
result_df.to_excel(output_path, index=False)
print("步骤10: 保存须复核或须整改的数据...")
source_file = os.path.join(temp_dir, '糖尿病_提醒.xlsx')
target_file = os.path.join(output_dir, '糖尿病_须复核或整改问题汇总.xlsx')
os.makedirs(output_dir, exist_ok=True)
try:
    df = pd.read_excel(source_file, engine='openpyxl') 
except:
    df = pd.read_excel(source_file)
df.sort_values(by=['所属管辖', '姓名'], inplace=True)
df.to_excel(target_file, index=False)

print("-" * 50)
print(f"人数: {len(result_df)}")
print(f"规范数: {len(result_df[result_df['总规范性']=='规范'])}")
print(f"不规范数: {len(result_df[result_df['总规范性']=='不规范'])}")

####################################################################################################
##此段代码为各类数据统计-2025.08.11修改
####################################################################################################
import pandas as pd
import numpy as np
from tqdm import tqdm
import os
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.styles import Border, Side, Alignment, Font, PatternFill
from openpyxl.utils import get_column_letter

# 文件路径配置
follow_up_file = '糖尿病随访明细.xlsx'
summary_file = os.path.join('糖尿病_核查汇总', '糖尿病_规范核查结果汇总.xlsx')
output_file = os.path.join('糖尿病_核查汇总', '糖尿病_规范核查结果统计.xlsx')

print("=" * 60)
print("【糖尿病规范率统计】...2025.08.11...")
print("功能说明:")
print("  1. 提取所需数据")
print("  2. 统计各类数据")
print("-" * 50)
print("步骤1: 正在进行各类数据的统计...")
# 读取随访明细数据
follow_up_df = pd.read_excel(follow_up_file, engine='openpyxl')
# 读取核查汇总数据
summary_df = pd.read_excel(summary_file, engine='openpyxl')

print("步骤2: 正在统计末次随访控制满意数...")
# 处理随访数据 - 找出每个人最后一次随访状态
last_follow_up = (
    follow_up_df.sort_values(by=['身份证号', '随访日期'])
    .groupby('身份证号', as_index=False)
    .last()
)

# 合并数据集
merged_df = summary_df.merge(
    last_follow_up[['身份证号', '随访分类']], 
    on='身份证号', 
    how='left',
    suffixes=('', '_follow')
)

# 创建计算需要的列
merged_df['血糖达标'] = merged_df['随访分类'] == '控制满意'
merged_df['65及以上'] = merged_df['年龄'] >= 65
merged_df['65以下'] = merged_df['年龄'] < 65
merged_df['总规范性_规范'] = merged_df['总规范性'] == '规范'
merged_df['一致性_一致'] = merged_df['一致性(是否一致)'] == '是'
merged_df['一致性_不一致'] = merged_df['一致性(是否一致)'] == '否'
merged_df['体检_是'] = merged_df['体检(是否体检)'] == '是'
merged_df['体检_公卫'] = merged_df['体检(公卫|其他)'] == '公卫'
merged_df['体检_其他'] = merged_df['体检(公卫|其他)'] == '其他'
merged_df['体检表_规范'] = merged_df['体检表(是否规范)'] == '是'
merged_df['随访_频次不达标'] = merged_df['随访(频次)'] == '不达标'
merged_df['随访_规范'] = merged_df['随访(是否规范)'] == '是'

# 获取唯一管辖区域
jurisdictions = summary_df['所属管辖'].unique().tolist()

results = []
print("步骤3: 按管辖区域计算指标...")
pbar = tqdm(jurisdictions, desc="  统计")
for jur in pbar:
    pbar.set_postfix_str(jur)
    jur_data = merged_df[merged_df['所属管辖'] == jur]
    total_count = len(jur_data)  
    
    # 计算各项指标
    metrics = {
        '所属管辖': jur,
        '糖尿病管理数': total_count,
        '65及以上数': jur_data['65及以上'].sum(),
        '65以下数': jur_data['65以下'].sum(),
        '末次血糖达标数': jur_data['血糖达标'].sum(),
        '末次血糖达标率': round(jur_data['血糖达标'].sum() / total_count, 4) if total_count else 0,
        '总规范数': jur_data['总规范性_规范'].sum(),
        '总规范率': round(jur_data['总规范性_规范'].sum() / total_count, 4) if total_count else 0,
        '一致数': jur_data['一致性_一致'].sum(),
        '一致率': round(jur_data['一致性_一致'].sum() / total_count, 4) if total_count else 0,
        '不一致数': jur_data['一致性_不一致'].sum(),
        '三史不一致数': jur_data['一致性(既往史)'].notna().sum(),
        '同天随访体检不一致数': jur_data['一致性(同天随访体检)'].notna().sum(),
        '同天两病随访不一致数': jur_data['一致性(同天两病随访)'].notna().sum(),
        '体检与近期随访用药不一致数': jur_data['一致性(体检近期随访用药)'].notna().sum(),
        '体检数': jur_data['体检_是'].sum(),
        '公卫体检数': jur_data['体检_公卫'].sum(),
        '其他体检数': jur_data['体检_其他'].sum(),
        '65以下未体检数': jur_data[(jur_data['65以下']) & ~jur_data['体检_是']].shape[0],
        '现存主要健康问题数': jur_data['体检表(现存主要健康问题)'].notna().sum(),
        '健康评价问题数': jur_data['体检表(健康评价)'].notna().sum(),
        '健康指导问题数': jur_data['体检表(健康指导)'].notna().sum(),
        '体检表其他问题数': jur_data['体检表(其他问题)'].notna().sum(),
        '体检表规范数': jur_data['体检表_规范'].sum(),
        '体检表规范率': round(jur_data['体检表_规范'].sum() / jur_data['体检_是'].sum(), 4) if jur_data['体检_是'].sum() else 0,
        '随访频次不达标数': jur_data['随访_频次不达标'].sum(),
        '转诊增访问题数': jur_data['随访(转诊增访)'].notna().sum(),
        '随访空错项数': jur_data['随访(其他)'].notna().sum(),#前面将空错项问题写入到了其他里去了(08.07)
        '随访规范数': jur_data['随访_规范'].sum(),#（08.07）
        '随访规范率': round(jur_data['随访_规范'].sum() / total_count, 4) if total_count else 0,
    }
    
    results.append(metrics)

# 创建结果DataFrame
results_df = pd.DataFrame(results)

# 添加合计行
total_management = results_df['糖尿病管理数'].sum()
total_metrics = {
    '所属管辖': '合计',
    '糖尿病管理数': total_management,
    '65及以上数': results_df['65及以上数'].sum(),
    '65以下数': results_df['65以下数'].sum(),
    '末次血糖达标数': results_df['末次血糖达标数'].sum(),
    '末次血糖达标率': round(results_df['末次血糖达标数'].sum() / total_management, 4) if total_management else 0,
    '总规范数': results_df['总规范数'].sum(),
    '总规范率': round(results_df['总规范数'].sum() / total_management, 4) if total_management else 0,
    '一致数': results_df['一致数'].sum(),
    '一致率': round(results_df['一致数'].sum() / total_management, 4) if total_management else 0,
    '不一致数': results_df['不一致数'].sum(),
    '三史不一致数': results_df['三史不一致数'].sum(),
    '同天随访体检不一致数': results_df['同天随访体检不一致数'].sum(),
    '同天两病随访不一致数': results_df['同天两病随访不一致数'].sum(),
    '体检与近期随访用药不一致数': results_df['体检与近期随访用药不一致数'].sum(),
    '体检数': results_df['体检数'].sum(),
    '公卫体检数': results_df['公卫体检数'].sum(),
    '其他体检数': results_df['其他体检数'].sum(),
    '65以下未体检数': results_df['65以下未体检数'].sum(),
    '现存主要健康问题数': results_df['现存主要健康问题数'].sum(),
    '健康评价问题数': results_df['健康评价问题数'].sum(),
    '健康指导问题数': results_df['健康指导问题数'].sum(),
    '体检表其他问题数': results_df['体检表其他问题数'].sum(),
    '体检表规范数': results_df['体检表规范数'].sum(),
    '体检表规范率': round(results_df['体检表规范数'].sum() / results_df['体检数'].sum(), 4) if results_df['体检数'].sum() else 0,
    '随访频次不达标数': results_df['随访频次不达标数'].sum(),
    '转诊增访问题数': results_df['转诊增访问题数'].sum(),
    '随访空错项数': results_df['随访空错项数'].sum(),
    '随访规范数': results_df['随访规范数'].sum(),
    '随访规范率': round(results_df['随访规范数'].sum() / total_management, 4) if total_management else 0,
}

# 添加合计行
results_df = pd.concat([
    results_df, 
    pd.DataFrame([total_metrics])
], ignore_index=True)

# 创建新的Excel工作簿
wb = Workbook()
ws = wb.active

# 设置表格标题行格式
header_fill = PatternFill(start_color="DDEBF7", end_color="DDEBF7", fill_type="solid")
header_font = Font(bold=True)
header_alignment = Alignment(wrap_text=True, vertical='center')  # 自动换行，添加垂直居中

# 定义颜色方案
green_fill = PatternFill(start_color="C6EFCE", end_color="C6EFCE", fill_type="solid")    # 浅绿色
yellow_fill = PatternFill(start_color="FFEB9C", end_color="FFEB9C", fill_type="solid")   # 浅黄色

# 绿色列名列表
green_columns = [
    '末次血糖达标数',
    '总规范数',
    '一致数',
    '体检数',
    '体检表规范数',
    '随访规范数'
]

# 黄色列名列表
yellow_columns = [
    '末次血糖达标率',
    '总规范率',
    '一致率',
    '体检表规范率',
    '随访规范率'
]

# 将DataFrame数据写入工作表
for r_idx, row in enumerate(dataframe_to_rows(results_df, index=False, header=True)):
    ws.append(row)
    
    # 设置第一行（标题行）格式
    if r_idx == 0:
        for cell in ws[1]:
            cell.fill = header_fill
            cell.font = header_font
            cell.alignment = header_alignment
            
            # 设置特定标题的绿色/黄色背景
            if cell.value in green_columns:
                cell.fill = green_fill
            elif cell.value in yellow_columns:
                cell.fill = yellow_fill

# 设置所有单元格格式
thin_border = Border(left=Side(style='thin'), 
                     right=Side(style='thin'), 
                     top=Side(style='thin'), 
                     bottom=Side(style='thin'))

# 应用边框和颜色
for row in ws.iter_rows(min_row=1, max_row=len(results_df)+1, max_col=len(results_df.columns)):
    for cell in row:
        cell.border = thin_border
        
        # 为数据单元格设置绿色/黄色背景
        if cell.row > 1:  # 跳过标题行
            if cell.value is not None and ws.cell(row=1, column=cell.column).value in green_columns:
                cell.fill = green_fill
            elif cell.value is not None and ws.cell(row=1, column=cell.column).value in yellow_columns:
                cell.fill = yellow_fill

# 设置列宽
# 所属管辖列宽10
ws.column_dimensions['A'].width = 10
# 其他列宽5.5
for col in range(2, len(results_df.columns)+1):
    col_letter = get_column_letter(col)
    ws.column_dimensions[col_letter].width = 6
ws.column_dimensions['F'].width = 7
ws.column_dimensions['H'].width = 7
ws.column_dimensions['J'].width = 7
ws.column_dimensions['Y'].width = 7
ws.column_dimensions['AD'].width = 7

# 设置百分比格式
percent_columns = ['末次血糖达标率', '总规范率', '一致率', '体检表规范率', '随访规范率']

# 获取标题行
header_row = next(ws.iter_rows(min_row=1, max_row=1, values_only=True))

# 设置百分比格式
for col_idx, header in enumerate(header_row, 1):
    if header in percent_columns:
        col_letter = get_column_letter(col_idx)
        for row_idx in range(2, ws.max_row + 1):
            cell = ws.cell(row=row_idx, column=col_idx)
            if isinstance(cell.value, (int, float)):
                cell.number_format = '0.00%'

# 保存工作簿
wb.save(output_file)
print(f"已保存: {output_file}")

####################################################################################################
##此段代码为随访数据初分析
####################################################################################################
import pandas as pd
from pathlib import Path
import numpy as np
from openpyxl import load_workbook
from openpyxl.styles import Alignment, Font
from openpyxl.utils import get_column_letter
from datetime import datetime

def main():
    # 文件路径设置
    input_file = Path.cwd() / '糖尿病随访明细.xlsx'
    output_dir = Path.cwd() / '糖尿病_核查汇总'
    output_dir.mkdir(exist_ok=True, parents=True)
    output_file = output_dir / '糖尿病_随访数据分析.xlsx'
    
    # 读取Excel数据
    print('将进行随访数据分析...')    
    df = pd.read_excel(input_file, dtype={'身份证号': str})
    
    # 日期转换
    df['随访日期'] = pd.to_datetime(df['随访日期'], errors='coerce')
    
    # 1. 按季度统计随访次数
    def get_quarter(date):
        if pd.isnull(date):
            return None
        month = date.month
        if month in [1, 2, 3]: return 1
        if month in [4, 5, 6]: return 2
        if month in [7, 8, 9]: return 3
        if month in [10, 11, 12]: return 4
        return None
    
    # 添加季度信息
    df['季度'] = df['随访日期'].apply(get_quarter)
    
    # 创建所有季度的列名
    q_columns = [f'Q{i}随访次数' for i in range(1, 5)]
    q_satisfied_columns = [f'Q{i}控制满意数' for i in range(1, 5)]
    
    # 按所属管辖统计季度随访次数
    q_counts = pd.crosstab(df['所属管辖'], df['季度'], dropna=False)
    # 确保有4列
    for q in range(1, 5):
        if q not in q_counts.columns:
            q_counts[q] = 0
    q_counts = q_counts[sorted(q_counts.columns)].fillna(0)
    q_counts.columns = q_columns
    
    # 2. 同日期最大随访条数统计
    date_counts = df.groupby(['所属管辖', '随访日期']).size().reset_index(name='计数')
    date_counts = date_counts.sort_values(['所属管辖', '计数', '随访日期'], 
                                      ascending=[True, False, False])
    
    # 获取每个管辖区域前5大的日期计数
    top_dates = date_counts.copy()
    top_dates.loc[:, '排名'] = top_dates.groupby('所属管辖').cumcount() + 1
    
    # 创建空的结果DataFrame
    max_table = pd.DataFrame(columns=['所属管辖'])
    for i in range(1, 6):
        # 只获取当前排名的数据
        rank_data = top_dates[top_dates['排名'] == i][['所属管辖', '计数', '随访日期']]
        rank_data.columns = ['所属管辖', f'Max{i}', f'MaxDate{i}']
        
        # 合并到结果
        if max_table.empty:
            max_table = rank_data
        else:
            max_table = max_table.merge(rank_data, on='所属管辖', how='outer')
    
    # 3. 按季度统计控制满意数
    df_sorted = df.sort_values(['身份证号', '随访日期'])
    last_records = df_sorted.groupby(['身份证号', '季度']).last().reset_index()
    
    # 筛选控制满意的记录
    satisfied = last_records[last_records['随访分类'] == '控制满意']
    q_satisfied = pd.crosstab(satisfied['所属管辖'], satisfied['季度'], dropna=False)
    # 确保有4列
    for q in range(1, 5):
        if q not in q_satisfied.columns:
            q_satisfied[q] = 0
    q_satisfied = q_satisfied[sorted(q_satisfied.columns)].fillna(0)
    q_satisfied.columns = q_satisfied_columns
    
    # 合并所有结果
    all_regions = df['所属管辖'].unique()
    results = pd.DataFrame({'所属管辖': all_regions})
    
    # 逐步合并统计结果（不再包含症状和服药人数统计）
    results = results.merge(q_counts, on='所属管辖', how='left')
    results = results.merge(max_table, on='所属管辖', how='left')
    results = results.merge(q_satisfied, on='所属管辖', how='left')
    
    # 填充NaN值为0
    results.fillna(0, inplace=True)
    
    # 添加汇总行
    numeric_cols = results.select_dtypes(include=[np.number]).columns
    # 排除日期列（MaxDate1-5）
    numeric_cols = [col for col in numeric_cols if not col.startswith('MaxDate')]
    
    summary_row = {'所属管辖': '汇总'}
    for col in numeric_cols:
        summary_row[col] = results[col].sum()
    for col in results.columns:
        if col.startswith('MaxDate'):
            summary_row[col] = ''
    
    summary_df = pd.DataFrame([summary_row])
    results = pd.concat([results, summary_df], ignore_index=True)
    
    # 1. 先将结果保存为Excel文件
    results.to_excel(output_file, index=False)
    print(f"数据分析完成...")
    
    wb = load_workbook(output_file)
    ws = wb.active
    
    # 1. 设置第一行自动换行
    for cell in ws[1]:
        cell.alignment = Alignment(wrap_text=True, vertical='center')
    
    # 2. 设置日期列格式和列宽（重点优化）
    date_col_indices = {}
    for col_idx, cell in enumerate(ws[1], 1):
        if cell.value and cell.value.startswith('MaxDate'):
            date_col_indices[cell.value] = col_idx
    
    # 应用日期格式和列宽设置
    date_style = 'yyyy-mm-dd'  # YYYY-MM-DD格式
    for col_name, col_idx in date_col_indices.items():
        col_letter = get_column_letter(col_idx)
        
        # 设置列宽为14
        ws.column_dimensions[col_letter].width = 14
        
        # 格式化日期列
        for row_idx in range(2, ws.max_row + 1):
            cell = ws.cell(row=row_idx, column=col_idx)
            if cell.value and cell.value != '':
                # 处理不同的日期格式
                if isinstance(cell.value, datetime):
                    cell.number_format = date_style
                elif isinstance(cell.value, pd.Timestamp):
                    cell.value = cell.value.to_pydatetime()
                    cell.number_format = date_style
                elif isinstance(cell.value, str):
                    try:
                        # 尝试转换字符串为日期
                        dt_value = datetime.strptime(cell.value, "%Y-%m-%d")
                        cell.value = dt_value
                        cell.number_format = date_style
                    except:
                        pass
    
    # 3. 设置其他列宽第一列宽度为15
    ws.column_dimensions['A'].width = 15
    
    # 设置其他非日期列的列宽为6
    for col_idx in range(2, ws.max_column + 1):
        col_letter = get_column_letter(col_idx)
        header_cell = ws.cell(row=1, column=col_idx).value
        
        # 跳过已经设置宽度的日期列
        if not header_cell or not header_cell.startswith('MaxDate'):
            ws.column_dimensions[col_letter].width = 6
    
    # 4. 设置汇总行格式
    last_row = ws.max_row
    bold_font = Font(bold=True)
    for col_idx in range(1, ws.max_column + 1):
        cell = ws.cell(row=last_row, column=col_idx)
        cell.font = bold_font
        cell.alignment = Alignment(horizontal='center')
    
    # 保存格式化后的Excel
    wb.save(output_file)

if __name__ == "__main__":
    main()

####################################################################################################
import os
import shutil

def copy_files_to_target():
    source_dir = '临时_核查结果'
    target_dir = '糖尿病_核查汇总'
    
    if not os.path.exists(target_dir):
        os.makedirs(target_dir)
        print(f"已创建目标文件夹: {target_dir}")
    
    files_to_copy = [ '高血压随访明细_已删除.xlsx', '糖尿病随访明细_已删除.xlsx','体检明细表_删重删空.xlsx']
    
    copied_count = 0
    for file in files_to_copy:
        source_path = os.path.join(source_dir, file)
        target_path = os.path.join(target_dir, file)
        
        if os.path.exists(source_path):
            shutil.copy(source_path, target_path)
            copied_count += 1
        else:
            print(f"文件不存在: {source_path}，跳过复制")

# 执行函数
copy_files_to_target()
####################################################################################################
import os
import pandas as pd
from openpyxl import load_workbook

# 1. 读取居民档案建立映射字典
resident_df = pd.read_excel('居民档案.xlsx', dtype=str)
# 创建身份证号->健康档案号的映射字典
id_to_health_dict = resident_df.set_index('身份证号')['健康档案号'].to_dict()

# 2. 定义处理函数
def process_file(file_path, save_folder, new_file_name):
    """处理单个文件：更换列名并转换身份证号为健康档案号"""
    # 读取Excel文件
    df = pd.read_excel(file_path, dtype=str)
    
    # 检查是否存在身份证号列
    if '身份证号' not in df.columns:
        print(f"警告: 文件 {os.path.basename(file_path)} 中缺少 '身份证号' 列")
        return
    
    # 创建健康档案号列
    df['健康档案号'] = df['身份证号'].map(id_to_health_dict).fillna('')
    
    # 删除原身份证号列
    df.drop(columns=['身份证号'], inplace=True)
    
    # 调整列顺序（健康档案号放到身份证号的位置）
    columns = list(df.columns)
    columns.remove('健康档案号')
    new_col_position = columns.index('所属管辖') + 1 if '所属管辖' in columns else 0
    columns.insert(new_col_position, '健康档案号')
    df = df[columns]
    
    # 保存新文件
    new_file_path = os.path.join(save_folder, new_file_name)
    df.to_excel(new_file_path, index=False)
    
    return df

# 3. 定义拆分函数
def split_by_region(df, save_folder, base_name):
    """按所属管辖拆分文件"""
    if '所属管辖' not in df.columns:
        print(f"错误: 文件缺少 '所属管辖' 列")
        return
    
    # 为每个区域创建前缀并分组
    df['区域前缀'] = df['所属管辖'].str[:2]
    grouped = df.groupby('区域前缀')
    
    for region_prefix, group in grouped:
        # 删除临时区域前缀列
        group = group.drop(columns=['区域前缀'])
        
        # 构建文件名
        file_name = f"{base_name}_{region_prefix}.xlsx"
        file_path = os.path.join(save_folder, file_name)
        
        # 保存区域文件
        group.to_excel(file_path, index=False)

# 4. 主程序
if __name__ == "__main__":
    # 设置文件夹路径
    base_folder = '糖尿病_核查汇总'
    village_folder = os.path.join(base_folder, '糖尿病规范核查问题_分村')
    
    # 创建分村文件夹（如果不存在）
    os.makedirs(village_folder, exist_ok=True)
    
    try:
        # 处理规范核查结果汇总文件
        print('-' * 50 )
        print( '将对问题按村进行拆分...' )
        result_path = os.path.join(base_folder, '糖尿病_规范核查结果汇总.xlsx')
        result_df = process_file(
            result_path,
            base_folder,
            '糖尿病_规范核查结果汇总_档案号.xlsx'
        )
        
        # 处理须复核问题汇总文件
        review_path = os.path.join(base_folder, '糖尿病_须复核或整改问题汇总.xlsx')
        review_df = process_file(
            review_path,
            base_folder,
            '糖尿病_须复核或整改问题汇总_档案号.xlsx'
        )
        
        # 拆分文件
        if result_df is not None:
            split_by_region(
                result_df,
                village_folder,
                '糖尿病_规范核查结果汇总_档案号'
            )
        
        if review_df is not None:
            split_by_region(
                review_df,
                village_folder,
                '糖尿病_须复核或整改问题汇总_档案号'
            )
        
        print("拆分完成！")
        
    except Exception as e:
        print(f"处理过程中出错: {str(e)}")

####################################################################################################
####################################################################################################
import pandas as pd
import json

print("=" * 60)
print("【糖尿病规范数据可视化】...2025.09.11...")
print("功能说明:")
print("  1. 提取所需数据")
print("  2. 数据转html可视化")
print("-" * 50)

# 读取Excel文件
df = pd.read_excel('糖尿病_核查汇总/糖尿病_规范核查结果统计.xlsx')

# 重命名列以匹配数据（因为原始列名包含特殊字符）
column_rename = {
    '所属管辖': 'region',
    '糖尿病管理数': 'hypertension_total',
    '65及以上数': 'age_65_plus',
    '65以下数': 'age_below_65',
    '末次血糖达标数': 'last_bp_standard',
    '末次血糖达标率': 'last_bp_standard_rate',
    '总规范数': 'total_standard',
    '总规范率': 'total_standard_rate',
    '一致数': 'consistent_count',
    '一致率': 'consistent_rate',
    '不一致数': 'inconsistent_count',
    '三史不一致数': 'history_inconsistent',
    '同天随访体检不一致数': 'same_day_followup_inconsistent',
    '同天两病随访不一致数': 'same_day_double_disease_inconsistent',
    '体检与近期随访用药不一致数': 'physical_drug_inconsistent',
    '体检数': 'physical_exam_total',
    '公卫体检数': 'public_health_exam',
    '其他体检数': 'other_exam',
    '65以下未体检数': 'below_65_unexamined',
    '现存主要健康问题数': 'health_issues',
    '健康评价问题数': 'health_assessment_issues',
    '健康指导问题数': 'health_guidance_issues',
    '体检表其他问题数': 'other_physical_issues',
    '体检表规范数': 'physical_standard_count',
    '体检表规范率': 'physical_standard_rate',
    '随访频次不达标数': 'followup_frequency_noncompliance',
    '转诊增访问题数': 'referral_issues',
    '随访空错项数': 'followup_missing_errors',
    '随访规范数': 'followup_standard_count',
    '随访规范率': 'followup_standard_rate'
}

df = df.rename(columns=column_rename)

# 过滤掉region为'合计'的行
df = df[df['region'] != '合计']

# 转换为字典列表格式
data_dict = df.to_dict(orient='records')

# 生成JSON文件
with open('糖尿病_核查汇总/hypertension_data_out_region.json', 'w', encoding='utf-8') as f:
    json.dump(data_dict, f, ensure_ascii=False, indent=2)

print("步骤1: JSON文件已生成：hypertension_data_out_region.json")
#---------------------------------将数据转json--------------------------------
import os
import json
from bs4 import BeautifulSoup

# 定义文件路径
standard_html_path = os.path.join('standard', 'dm_index.html')
json_data_path = os.path.join('糖尿病_核查汇总', 'hypertension_data_out_region.json')
output_path = os.path.join('糖尿病_核查汇总', 'dm_index.html')

# 读取原始HTML文件
with open(standard_html_path, 'r', encoding='utf-8') as f:
    html_content = f.read()

# 解析HTML
soup = BeautifulSoup(html_content, 'html.parser')

# 查找目标script标签
target_script = soup.find('script', {'id': 'jsonData', 'type': 'application/json'})
if not target_script:
    raise ValueError("未找到id='jsonData'的script标签")

# 读取新的JSON数据
with open(json_data_path, 'r', encoding='utf-8') as f:
    new_json_data = json.load(f)  # 先解析确保JSON有效
    new_json_str = json.dumps(new_json_data, ensure_ascii=False)

# 替换script内容
target_script.string = new_json_str

# 保存修改后的HTML
with open(output_path, 'w', encoding='utf-8') as f:
    f.write(str(soup))

print(f"文件已成功保存至: {output_path}")

#----------------------------默认浏览器打开前复制样式文件--------------------------
import shutil
import os
import webbrowser
source_folder = 'standard/css'
target_parent = '糖尿病_核查汇总'
target_folder = os.path.join(target_parent, 'css')

os.makedirs(target_parent, exist_ok=True)
shutil.copytree(src=source_folder,dst=target_folder,dirs_exist_ok=True)  

folder = "糖尿病_核查汇总"
filename = "dm_index.html"
target_path = os.path.join(folder, filename)

if not os.path.exists(target_path):
    print(f"错误：文件 '{target_path}' 不存在")
else:
    abs_path = os.path.abspath(target_path)
    file_url = f"file:///{abs_path.replace(os.sep, '/')}"
    
    webbrowser.open(file_url)
print("步骤2: 数据看板已打开")
####################################################################################################
print("*" * 80)
print("感谢您的使用，本代码已运行结束！")
print("源代码欢迎用户更新、优化-2025.09.11 18:25")
print("                        ——郁进（微信同号） 13606278090")
print("*" * 80)
####################################################################################################
####################################################################################################